SONOTRIGGER SOFTWARE: Consultas personalizadas de WSPRnet: TX

Consultas personalizadas de WSPRnet: Introducción

Para conocer el número de spots recibidos por la red WSPRnet en un día (p.e. 13/6/2025) entramos en WSPR.ROCKS y ejecutamos la sentencia SQL "SELECT" "SELECT COUNT(*)" desde las 00h00 del 13/6/2025 a las 00h00 del 14/6/2025 "AND time > '2025-06-13' AND time < '2025-06-14'" de la siguiente forma:

SELECT COUNT(*)
FROM wspr.rx
WHERE time > '2025-06-13' AND time < '2025-06-14';

El número de spots recibidos en un día como el 13/6/2025 fue de 3.562.511

Si son los spots recibidos desde una fecha hasta el momento actual podemos usar la claúsula NOW() y el dia actual como CURDATE()

SELECT COUNT(*)
FROM wspr.rx
WHERE time > '2025-06-13' AND time < NOW();

SELECT COUNT(*)
FROM wspr.rx
WHERE time >= CURDATE() AND time < NOW();

Los spots de ayer mediante dos formulas equivalentes una de forma explicita con las fechas y otra de forma implicita restándole 1 día a la fecha actual DATEADD(day, -1, CURDATE()) y la fecha actual CURDATE()

SELECT COUNT(*)
FROM wspr.rx
WHERE time >= '2025-06-23' AND time < '2025-06-24';

SELECT COUNT(*)
FROM wspr.rx
WHERE time >= DATEADD(day, -1, CURDATE()) AND time < CURDATE();

Ultima hora

SELECT COUNT(*)
FROM wspr.rx
WHERE time >= NOW() - INTERVAL 1 HOUR AND time < NOW();

Si se quiere conocer el numero de estaciones TX WSPR de las que se han recibido spots en un día en concreto:

SELECT COUNT(DISTINCT tx_sign)
FROM wspr.rx
WHERE time > '2025-06-13' AND time < '2025-06-14'

El número de estaciones WSPR TX de las cuales se reciben spots en un día como el 13/6/2025 fue de 5.010, la media es de 711 spots/estación con un máximo de 167.328 de la estación WW0WWV (https://wwvarc.org/)

Si se quiere conocer el numero de spots recibidos de cada estación se puede hacer:

SELECT tx_sign, COUNT(*)
FROM wspr.rx
WHERE time > '2025-06-13' AND time < '2025-06-14'
GROUP BY tx_sign
ORDER BY COUNT(*) DESC;

Como hay mas de 2.000 filas de resultado se genera un fichero descargable.

Si queremos conocer el orden que ocupa cada estación hay que incluir una columna "row_number() OVER (ORDER BY COUNT(*) DESC) AS row_num":

SELECT row_number() OVER (ORDER BY COUNT(*) DESC) AS row_num, tx_sign, COUNT(*) AS total
FROM wspr.rx
WHERE time > '2025-06-13' AND time < '2025-06-14'
GROUP BY tx_sign
ORDER BY COUNT(*) DESC;

Ahora vamos a indagar cuales son las 10 estaciones WSPR TX de las cuales hemos recibido mas spots

SELECT rx_sign, COUNT(*) FROM wspr.rx
WHERE time > '2025-05-17' AND time < NOW()
AND tx_sign ='EA5JTT'
GROUP BY rx_sign
ORDER BY COUNT(*) DESC
LIMIT 10;

Si quitamos la sentencia "LIMIT 10" nos saldrían la totalidad de estaciones que han recibido los spots emitidos por EA5JTT que en este caso era de 1.600

¿Cuantas estaciones WSPR TX hay activas en un periodo de tiempo?

SELECT COUNT(DISTINCT tx_sign)
FROM wspr.rx
WHERE
time > '2025-05-01' AND time < NOW()

El numero es una exageración al ncluirse los indicativos erroneos y la telemetria de los globos

Excluyendo los indicativos que comienzan por 0, 1 y Q tenemos

SELECT COUNT(DISTINCT tx_sign)
FROM wspr.rx
WHERE
tx_sign NOT LIKE '0%' AND
tx_sign NOT LIKE '1%' AND
tx_sign NOT LIKE 'Q%' AND
time > '2025-05-01' AND
time < NOW();

Las activas en lo que llevamos de dia que no llegan a 3.000

SELECT COUNT(DISTINCT tx_sign)
FROM wspr.rx
WHERE
tx_sign NOT LIKE '0%' AND
tx_sign NOT LIKE '1%' AND
tx_sign NOT LIKE 'Q%' AND
time > CURDATE() AND
time < NOW();

Si se quiere conocer cuantas estaciones hay de EA(España)

SELECT tx_sign, COUNT(*)
FROM wspr.rx
WHERE tx_sign LIKE 'EA%' AND time > '2025-06-13' AND time < '2025-06-14'
GROUP BY tx_sign
ORDER BY COUNT(*) DESC;

Hay 16 estaciones WSPR TX en EA (España)
Hay distritos sin ninguna estación WSPX TX como EA2, EA4, EA7, EA8 y EA9

Mas completo es este código para conocer los spots recibidos de las 10 primeras estaciones de España en lo que llevamos de mes de agosto.

SELECT tx_sign, COUNT(*)
FROM wspr.rx
WHERE (tx_sign LIKE 'EA%' OR tx_sign LIKE 'EB%' OR tx_sign LIKE 'EC%' OR tx_sign LIKE 'ED%' OR tx_sign LIKE 'EE%' OR tx_sign LIKE 'EF%')
AND time > '2025-08-01'
AND time < NOW()
GROUP BY tx_sign
ORDER BY COUNT(*) DESC
LIMIT 10;

Estaciones españolas que durante el mes de agosto de 2025 han recibido spots de EA5JTT

SELECT rx_sign, COUNT(*)
FROM wspr.rx
WHERE
tx_sign = 'EA5JTT'
AND (rx_sign LIKE 'EA%' OR tx_sign LIKE 'EB%' OR tx_sign LIKE 'EC%' OR tx_sign LIKE 'ED%' OR tx_sign LIKE 'EE%' OR tx_sign LIKE 'EF%')
AND time > '2025-08-01'
AND time < NOW()
GROUP BY rx_sign
ORDER BY COUNT(*) DESC

Se puede limitar únicamente a los recibidos de una determinada estación (p.e. EA5JTT ) mediante "WHERE tx_sign='EA5JTT'" de la siguiente forma

SELECT COUNT(*)
FROM wspr.rx
WHERE tx_sign='EA5JTT' AND time > '2025-06-13' AND time < '2025-06-14';

Si se quiere es ver los registros completos (SELECT *) de un intervalo, por ejemplo desde las 03h00 a las 4h00 se puede usar

SELECT *
FROM wspr.rx
WHERE tx_sign='EA5JTT'
AND time > '2025-06-14 03:00:00'
AND time < '2025-06-14 04:00:00';

El listado anterior ordenado por tiempo

SELECT * FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-22 15:00:00' AND time < '2025-06-22 17:00:00' ORDER BY HOUR(time);

El listado anterior ordenado por tiempo y descartando duplicados

SELECT DISTINCT rx_sign FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-23 00:00:00' AND time < '2025-06-23 23:59:59' ORDER BY HOUR(time);

El listado de  estaciones receptoras únicas con el numero de spots recibidos cada una

SELECT DISTINCT rx_sign, COUNT(rx_sign) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-06-23 00:00:00' AND time < '2025-06-23 23:59:59' GROUP BY rx_sign HAVING COUNT(rx_sign) > 1 ORDER BY COUNT(rx_sign) DESC;

Listado de estaciones diferentes que han recibidas nuestros WSPR (1,484 hasta el 9/8/2025 con WSPR TX 80to10 de Zachtek)

SELECT DISTINCT rx_sign
FROM wspr.rx
WHERE tx_sign='EA5JTT'
AND time > '2025-06-01'
AND time < NOW()
ORDER BY rx_sign;

Formateado

SELECT DISTINCT CONCAT('"', rx_sign, '",') AS formatted_rx_sign
FROM wspr.rx
WHERE tx_sign='EA5JTT'
AND time > '2025-06-01'
AND time < NOW()
ORDER BY rx_sign;

Ahora vamos a seleccionar de las últimas 24h "where time > subtractHours(now(), 24) " aquellas estaciones (quitando repeticiones) que han recibido registros recibidos por WSPR que ha transmitido EA5JTT "and tx_sign like 'EA5JTT%'"

select tx_sign, count() as total_spots, count(distinct rx_sign) as uniqs, groupArray(distinct code) as mode_code from wspr.rx where time > subtractHours(now(), 24) and tx_sign like 'EA5JTT%' group by tx_sign order by total_spots desc

En las últimas 24h 204 estaciones han recibido un total de 3.154 spots emitidos por EA5JTT

Si queremos sacar el detalle de las banda en las que se han recibido spots modificamos ligeramente la query

select tx_sign, count() as total_spots, count(distinct rx_sign) as uniqs, groupArray(distinct band) as band from wspr.rx where time > subtractHours(now(), 24) and tx_sign like 'EA5JTT%' group by tx_sign order by total_spots desc

Listado de spots recibidos por una estación desde una fecha y hora hasta la actualidad

SELECT * FROM wspr.rx WHERE rx_sign='EA5JTT' AND time > '2025-06-14 03:00:00' AND time < NOW() ORDER BY HOUR(time);

Estaciones receptoras /transmisoras de WSPR en una cuadricula determinada (Con 4 caracteres estan las que transmiten con 4 caracteres y las de que transmiten con 6)

SELECT tx_loc, COUNT(*) FROM wspr.rx WHERE tx_loc LIKE 'IM99%' AND time > '2025-06-13' AND time < '2025-06-25' GROUP BY tx_loc ORDER BY COUNT(*) DESC;

Si queremos concocer los spots de un dia concreto 23-6-2025 recibidos de los transmitidos en dos cuadriculas por ejemplo "IM" e "IN"

SELECT tx_loc, COUNT(*) FROM wspr.rx WHERE (tx_loc LIKE 'IM%' OR tx_loc LIKE 'IN%') AND time > '2025-06-23' AND time < '2025-06-24' GROUP BY tx_loc ORDER BY COUNT(*) DESC;

Los recibidos en cada cuadricula ordenados por cuadriculas

SELECT rx_loc, COUNT(*) FROM wspr.rx WHERE (rx_loc LIKE 'IM%' OR rx_loc LIKE 'IN%') AND time > '2025-06-23' AND time < '2025-06-24' GROUP BY rx_loc ORDER BY rx_loc DESC;

Si lo que se quiere son los subtotales por bandas (GROUP BY band) con indicación del valor de la misma entonces tenemos:

SELECT band, COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY band;

Rapidamente se entiende la popularidad de la banda de 14 MHz (20m)

Si se quieren los subtotales por días

SELECT DATE(time), COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY DATE(time);

Por horas para el dia 11/5/2025

SELECT HOUR(time), COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY HOUR(time);

Distribución por horas y bandas de los spots recibidos de EA5JTT en todas las bandas ordenado por horas y bandas

SELECT HOUR(time), band, COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY HOUR(time), band ORDER BY HOUR(time), band;

Distribución por horas de los spots recibidos de EA5JTT en la banda de 14 MHz

SELECT HOUR(time), COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND band='14' GROUP BY HOUR(time), band ORDER BY HOUR(time), band;

Distribución por horas y bandas

SELECT HOUR(time) AS hora, COUNT(CASE WHEN band = '3' THEN 1 END) AS 80m, COUNT(CASE WHEN band = '7' THEN 1 END) AS 40m, COUNT(CASE WHEN band = '10' THEN 1 END) AS 30m, COUNT(CASE WHEN band = '14' THEN 1 END) AS 20m, COUNT(CASE WHEN band = '18' THEN 1 END) AS 17m, COUNT(CASE WHEN band = '21' THEN 1 END) AS 15m, COUNT(CASE WHEN band = '24' THEN 1 END) AS 12m, COUNT(CASE WHEN band = '28' THEN 1 END) AS 10m, COUNT(CASE WHEN band <> '99' THEN 1 END) AS TOTAL FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-06-23' AND time < '2025-06-24' GROUP BY HOUR(time) ORDER BY HOUR(time);

Distribución de spots recibidos en WSPRnet agrupados por bandas y horas de una estación en lo que llevamos de día

SELECT HOUR(time) AS hora,
COUNT(CASE WHEN band = '3' THEN 1 END) AS 80m,
COUNT(CASE WHEN band = '7' THEN 1 END) AS 40m,
COUNT(CASE WHEN band = '10' THEN 1 END) AS 30m,
COUNT(CASE WHEN band = '14' THEN 1 END) AS 20m,
COUNT(CASE WHEN band = '18' THEN 1 END) AS 17m,
COUNT(CASE WHEN band = '21' THEN 1 END) AS 15m,
COUNT(CASE WHEN band = '24' THEN 1 END) AS 12m,
COUNT(CASE WHEN band = '28' THEN 1 END) AS 10m,
COUNT(CASE WHEN band <> '99' THEN 1 END) AS TOTAL
FROM wspr.rx WHERE tx_sign = 'EA5JTT'
AND time > CURDATE()
AND time < NOW()
GROUP BY HOUR(time)
ORDER BY HOUR(time);

Distribución por minutos en una hora totalizando, separado por bandas

SELECT
DATE_FORMAT(time, '%H:%i') AS hora_minuto,
COUNT(CASE WHEN band = '3' THEN 1 END) AS "80m",
COUNT(CASE WHEN band = '7' THEN 1 END) AS "40m",
COUNT(CASE WHEN band = '10' THEN 1 END) AS "30m",
COUNT(CASE WHEN band = '14' THEN 1 END) AS "20m",
COUNT(CASE WHEN band = '18' THEN 1 END) AS "17m",
COUNT(CASE WHEN band = '21' THEN 1 END) AS "15m",
COUNT(CASE WHEN band = '24' THEN 1 END) AS "12m",
COUNT(CASE WHEN band = '28' THEN 1 END) AS "10m",
COUNT(CASE WHEN band <> '99' THEN 1 END) AS total
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-08-12 05:00:00'
AND time < NOW()
GROUP BY hora_minuto
ORDER BY hora_minuto;

Distribuido por día y hora totalizando, y agrupado por bandas

SELECT
DATE_FORMAT(time, '%Y-%m-%d %H') AS dia_hora,
COUNT(CASE WHEN band = '3' THEN 1 END) AS "80m",
COUNT(CASE WHEN band = '7' THEN 1 END) AS "40m",
COUNT(CASE WHEN band = '10' THEN 1 END) AS "30m",
COUNT(CASE WHEN band = '14' THEN 1 END) AS "20m",
COUNT(CASE WHEN band = '18' THEN 1 END) AS "17m",
COUNT(CASE WHEN band = '21' THEN 1 END) AS "15m",
COUNT(CASE WHEN band = '24' THEN 1 END) AS "12m",
COUNT(CASE WHEN band = '28' THEN 1 END) AS "10m",
COUNT(CASE WHEN band <> '99' THEN 1 END) AS total
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-08-11 00:00:00'
AND time < NOW()
GROUP BY dia_hora
ORDER BY dia_hora;

Distribución por minutos en un día. En este ejemplo se observa el patron de emisión coordinado del proyecto International WSPR Project

SELECT MINUTE(time) AS Minutos, COUNT(CASE WHEN band = '3' THEN 1 END) AS 80m, COUNT(CASE WHEN band = '7' THEN 1 END) AS 40m, COUNT(CASE WHEN band = '10' THEN 1 END) AS 30m, COUNT(CASE WHEN band = '14' THEN 1 END) AS 20m, COUNT(CASE WHEN band = '18' THEN 1 END) AS 17m, COUNT(CASE WHEN band = '21' THEN 1 END) AS 15m, COUNT(CASE WHEN band = '24' THEN 1 END) AS 12m, COUNT(CASE WHEN band = '28' THEN 1 END) AS 10m, COUNT(CASE WHEN band <> '99' THEN 1 END) AS TOTAL FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-06-24 00:00:00' AND time < '2025-06-25 00:00:00' GROUP BY MINUTE(time) ORDER BY MINUTE(time);

Si se quieren los subtotales por dias y bandas y ordenados

SELECT DATE(time), band, COUNT(*) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY DATE(time), band ORDER BY DATE(time),band;

Si se quiere conocer los indicativos de las estaciones que han recibido nuestras transmisiones WSPR y el número de mensajes que han recibido y ordenada

SELECT rx_sign, COUNT(rx_sign) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY rx_sign ORDER BY COUNT(rx_sign) DESC;

Con subtotales por bandas

SELECT rx_sign,band, COUNT(rx_sign) FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY rx_sign, band ORDER BY COUNT(rx_sign) DESC;

Se puede obtener un encolumnado con suma por fila

SELECT DATE(time) AS fecha, COUNT(CASE WHEN band = '3' THEN 1 END) AS band_3, COUNT(CASE WHEN band = '7' THEN 1 END) AS band_7, COUNT(CASE WHEN band = '10' THEN 1 END) AS band_10, COUNT(CASE WHEN band = '14' THEN 1 END) AS band_14, COUNT(CASE WHEN band = '18' THEN 1 END) AS band_18, COUNT(CASE WHEN band = '21' THEN 1 END) AS band_21, COUNT(CASE WHEN band = '24' THEN 1 END) AS band_24, COUNT(CASE WHEN band = '28' THEN 1 END) AS band_28, COUNT(CASE WHEN band <> '99' THEN 1 END) AS total FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time >= '2025-05-11' AND time < '2025-06-12' GROUP BY DATE(time) ORDER BY DATE(time);

y sumar las columnas

SELECT toString(DATE(time)) AS fecha,
COUNT(CASE WHEN band = '3' THEN 1 END) AS band_3,
COUNT(CASE WHEN band = '7' THEN 1 END) AS band_7,
COUNT(CASE WHEN band = '10' THEN 1 END) AS band_10,
COUNT(CASE WHEN band = '14' THEN 1 END) AS band_14,
COUNT(CASE WHEN band = '18' THEN 1 END) AS band_18,
COUNT(CASE WHEN band = '21' THEN 1 END) AS band_21,
COUNT(CASE WHEN band = '24' THEN 1 END) AS band_24,
COUNT(CASE WHEN band = '28' THEN 1 END) AS band_28,
COUNT(CASE WHEN band <> '99' THEN 1 END) AS total
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-05-11'
AND time < NOW()
GROUP BY DATE(time)
UNION ALL
SELECT 'TOTAL' AS fecha, COUNT(CASE WHEN band = '3' THEN 1 END),
COUNT(CASE WHEN band = '7' THEN 1 END),
COUNT(CASE WHEN band = '10' THEN 1 END),
COUNT(CASE WHEN band = '14' THEN 1 END),
COUNT(CASE WHEN band = '18' THEN 1 END),
COUNT(CASE WHEN band = '21' THEN 1 END),
COUNT(CASE WHEN band = '24' THEN 1 END),
COUNT(CASE WHEN band = '28' THEN 1 END),
COUNT(CASE WHEN band <> '99' THEN 1 END)
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-05-11'
AND time < NOW()
ORDER BY fecha;

Obtener los modelos y versiones de los RX WAPR

SELECT version, COUNT(CASE WHEN version <> 'xxx' THEN 1 END) AS Total FROM wspr.rx WHERE tx_sign='EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' GROUP BY version ORDER BY version DESC;

con suma de total

SELECT * FROM ( SELECT version, COUNT(*) AS Total FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx' GROUP BY version UNION ALL SELECT 'TOTAL' AS version, COUNT(*) AS Total FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx') ORDER BY version = 'TOTAL', Total DESC;

con porcentaje

SELECT * FROM ( SELECT version, count(*) AS Total, round(count(*) / total.total_count * 100, 2) AS Porcentaje FROM wspr.rx,( SELECT count(*) AS total_count FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx' ) AS total WHERE tx_sign = 'EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx' GROUP BY version, total.total_count UNION ALL SELECT 'TOTAL' AS version, total_count AS Total, 100 AS Porcentaje FROM ( SELECT count(*) AS total_count FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-05-11' AND time < '2025-06-12' AND version <> 'xxx' ) ) ORDER BY version = 'TOTAL', Total DESC;

Una versión mas comparta que calcula los valores apra el día de ayer

SELECT * FROM ( SELECT version, count(*) AS Total, round(count(*) / total.total_count * 100, 2) AS Porcentaje FROM wspr.rx,( SELECT count(*) AS total_count FROM wspr.rx WHERE time > CURDATE() AND time < NOW() AND version <> 'xxx' ) AS total WHERE time > CURDATE() AND time < NOW() AND version <> 'xxx' GROUP BY version, total.total_count UNION ALL SELECT 'TOTAL' AS version, total_count AS Total, 100 AS Porcentaje FROM ( SELECT count(*) AS total_count FROM wspr.rx WHERE time > CURDATE() AND time < NOW() AND version <> 'xxx' ) ) ORDER BY version = 'TOTAL', Total DESC;

Vamos a hora a obtener las distancias máximas TX-RX para un dia concreto 23-6-2025, por horas y bandas (Recuerde que una gráfica, por ejemplo con EXCEL o una hoja de cálculo similar los datos se lven mejor)

SELECT HOUR(time) AS hora, MAX(CASE WHEN band = '3' THEN distance END) AS "80m", MAX(CASE WHEN band = '7' THEN distance END) AS "40m", MAX(CASE WHEN band = '10' THEN distance END) AS "30m", MAX(CASE WHEN band = '14' THEN distance END) AS "20m", MAX(CASE WHEN band = '18' THEN distance END) AS "17m", MAX(CASE WHEN band = '21' THEN distance END) AS "15m", MAX(CASE WHEN band = '24' THEN distance END) AS "12m", MAX(CASE WHEN band = '28' THEN distance END) AS "10m" FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time >= '2025-06-23' AND time < '2025-06-24' GROUP BY HOUR(time) ORDER BY HOUR(time);

Potencia de los los TX de los spots recibidos el dia 22-6-2025

SELECT power, COUNT(CASE WHEN power <> '999' THEN 1 END) AS Total FROM wspr.rx WHERE time > '2025-06-22' AND time < '2025-06-23' GROUP BY power ORDER BY power DESC;

De los recibidos ayer

SELECT power, COUNT(*) AS Total FROM wspr.rx WHERE time > CURDATE() AND time < NOW() AND power <> '999' GROUP BY power ORDER BY power DESC;

Ahora le añadimos una columna con el porcentaje

WITH ( SELECT count(*) FROM wspr.rx WHERE time > CURDATE() AND time < NOW() AND power <> '999') AS total_count SELECT * FROM (SELECT toString(power) AS power, count(*) AS Total, round(count(*) / total_count * 100, 2) AS Porcentaje FROM wspr.rx WHERE time > CURDATE() AND time < NOW() AND power <> '999' GROUP BY power UNION ALL SELECT 'TOTAL' AS power, total_count AS Total, 100.00 AS Porcentaje) ORDER BY power = 'TOTAL', Total DESC;

Valor absoluto de recepciones por parte de la red WSPR RX agrupados por banda para una estación en concreto y su porcentaje sobre el total

SELECT * FROM ( SELECT CAST(band AS String) AS band, COUNT(*) AS Total, ROUND(COUNT(*) / any(total.total_count) * 100, 2) AS Porcentaje FROM wspr.rx JOIN ( SELECT COUNT(*) AS total_count FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-06-28 16:30:00' AND time < '2025-06-29 16:30:00' AND band != '99' ) AS total ON 1=1 WHERE tx_sign = 'EA5JTT' AND time > '2025-06-28 16:30:00' AND time < '2025-06-29 16:30:00' AND band != '99' GROUP BY band UNION ALL SELECT 'TOTAL' AS band, total_count AS Total, 100 AS Porcentaje FROM ( SELECT COUNT(*) AS total_count FROM wspr.rx WHERE tx_sign = 'EA5JTT' AND time > '2025-06-28 16:30:00' AND time < '2025-06-29 16:30:00' AND band != '99' ) AS total) ORDER BY band = 'TOTAL' DESC, Total DESC;

En cuantos territorios ARRL DXCC se ha recibido nuestra señal WSPR (No está completo, el limite de entadas está en unas 100):

SELECT
CASE
WHEN LEFT(rx_sign, 2) = '3A' THEN 'Monaco'
WHEN LEFT(rx_sign, 2) = '4M' THEN 'Venezuela'
WHEN LEFT(rx_sign, 2) = '4V' THEN 'Haiti'
WHEN LEFT(rx_sign, 2) IN ('4X','4Z') THEN 'Israel'
WHEN LEFT(rx_sign, 2) IN ('5B', 'C4', 'P3') THEN 'Chipre'
WHEN LEFT(rx_sign, 2) = '5P' THEN 'Dinamarca'
WHEN LEFT(rx_sign, 2) = '9A' THEN 'Croacia'
WHEN LEFT(rx_sign, 2) = '9H' THEN 'Malta'
WHEN LEFT(rx_sign, 2) = 'C3' THEN 'Andorra'
WHEN LEFT(rx_sign, 2) IN ('BU','BV','BW','BX') THEN 'Taiwan'
WHEN LEFT(rx_sign, 1) = 'B' THEN 'China'
WHEN LEFT(rx_sign, 2) IN ('CA', 'CB', 'CC', 'CD', 'CE') THEN 'Chile'
WHEN LEFT(rx_sign, 2) = 'CN' THEN 'Marruecos'
WHEN LEFT(rx_sign, 2) IN ('CS','CT') THEN 'Portugal'
WHEN LEFT(rx_sign, 2) IN ('DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ','DR') THEN 'Alemania'
WHEN LEFT(rx_sign, 2) IN ('DU','DV','DW','DX','DY','DZ','4D','4E','4F','4G','4H','4I') THEN 'Filipinas'
WHEN LEFT(rx_sign, 3) IN ('EA6', 'EB6', 'EC6', 'ED6', 'EE6', 'EF6', 'EG6','EH6') THEN 'España - Baleares'
WHEN LEFT(rx_sign, 3) IN ('EA8', 'EB8', 'EC8', 'ED8', 'EE8', 'EF8', 'EG8','EH8') THEN 'España - Canarias'
WHEN LEFT(rx_sign, 3) IN ('EA9', 'EB9', 'EC9', 'ED9', 'EE9', 'EF9', 'EG9','EH9') THEN 'España - Ceuta/Melilla'
WHEN LEFT(rx_sign, 2) IN ('EA', 'EB', 'EC', 'ED', 'EE', 'EF', 'EG','EH') THEN 'España - Península'
WHEN LEFT(rx_sign, 2) = 'E5' THEN 'Islas Cook'
WHEN LEFT(rx_sign, 2) = 'E7' THEN 'Bosnia-Herzegovina'
WHEN LEFT(rx_sign, 2) = 'EW' THEN 'Bielorusia'
WHEN LEFT(rx_sign, 2) IN ('EI', 'EJ') THEN 'Irlanda'
WHEN LEFT(rx_sign, 2) = 'ES' THEN 'Estonia'
WHEN LEFT(rx_sign, 1) = 'F' THEN 'Francia'
WHEN LEFT(rx_sign, 2) IN ('GB', 'GX', 'MX','2E') THEN 'Inglaterra'
WHEN LEFT(rx_sign, 2) IN ('GI', 'GN', 'MI','2I') THEN 'Irlanda del Norte'
WHEN LEFT(rx_sign, 2) IN ('GJ', 'GH', 'MJ','2J') THEN 'Isla Jersey'
WHEN LEFT(rx_sign, 2) IN ('GM', 'GS', 'MM','2M') THEN 'Escocia'
WHEN LEFT(rx_sign, 2) IN ('GU', 'GP', 'MU','2U') THEN 'Guersey'
WHEN LEFT(rx_sign, 2) IN ('GW', 'GC', 'MW','2W') THEN 'Gales'
WHEN LEFT(rx_sign, 2) IN ('GD', 'GT', 'MD','2D') THEN 'Isla de Man'
WHEN LEFT(rx_sign, 1) = 'G' AND SUBSTRING(rx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(rx_sign, 2) IN ('HA', 'HG') THEN 'Hungria'
WHEN LEFT(rx_sign, 2) IN ('HC', 'HD') THEN 'Ecuador'
WHEN LEFT(rx_sign, 2) IN ('HJ', 'HK') THEN 'Colombia'
WHEN LEFT(rx_sign, 2) IN ('HO', 'HP') THEN 'Panama'
WHEN LEFT(rx_sign, 2) IN ('HQ', 'HR') THEN 'Honduras'
WHEN LEFT(rx_sign, 2) = 'HB' THEN 'Suiza'
WHEN LEFT(rx_sign, 2) = 'HH' THEN 'Haiti'
WHEN LEFT(rx_sign, 2) = 'HI' THEN 'Republica Dominicana'
WHEN LEFT(rx_sign, 2) = 'HL' THEN 'Republica Corea'
WHEN LEFT(rx_sign, 2) = 'HV' THEN 'Vaticano'
WHEN LEFT(rx_sign, 3) IN ('IS0', 'IM0') THEN 'Cerdeña'
WHEN LEFT(rx_sign, 1) = 'I' THEN 'Italia'
WHEN LEFT(rx_sign, 2) IN ('LA', 'LB','LC', 'LD', 'LE', 'LF', 'LG', 'LH', 'LI', 'LJ', 'LK', 'LL', 'LM', 'LN') THEN 'Noruega'
WHEN LEFT(rx_sign, 2) IN ('JA','JB','JC','JD','JE','JF','JG','JH','JI','JJ','JK','JL','JM','JN','JO','JP','JQ','JR','JS','7J','7K','7L','7N') THEN 'Japon'
WHEN LEFT(rx_sign, 2) = 'LX' THEN 'Luxemburgo'
WHEN LEFT(rx_sign, 2) = 'LZ' THEN 'Bulgaria'
WHEN LEFT(rx_sign, 1) = 'M' AND SUBSTRING(rx_sign, 2, 1) REGEXP '[0-9]' THEN 'Inglaterra'
WHEN LEFT(rx_sign, 2) = 'V5' THEN 'Namibia'
WHEN LEFT(rx_sign, 2) = 'OE' THEN 'Austria'
WHEN LEFT(rx_sign, 2) IN ('OK', 'OL') THEN 'Chequia'
WHEN LEFT(rx_sign, 2) IN ('OF', 'OG', 'OH', 'OI') THEN 'Finlandia'
WHEN LEFT(rx_sign, 2) = 'OM' THEN 'Eslovaquia'
WHEN LEFT(rx_sign, 2) IN ('ON', 'OO', 'OP','OQ', 'OR', 'OS','OT') THEN 'Belgica'
WHEN LEFT(rx_sign, 2) IN ('OU', 'OV', 'OW','OZ') THEN 'Dinamarca'
WHEN LEFT(rx_sign, 2) IN ('PA', 'PB', 'PC','PD', 'PE', 'PF', 'PG', 'PH', 'PI') THEN 'Paises Bajos'
WHEN LEFT(rx_sign, 2) IN ('PP','PQ','PR','PS','PT','PU','PV','PW','PX','PY','ZV','ZW','ZX','ZY','ZZ') THEN 'Brasil'
WHEN LEFT(rx_sign, 2) = 'PZ' THEN 'Surinam'
WHEN LEFT(rx_sign, 2) IN ('R1','R3','R4','R5','R6','R7') THEN 'Rusia Europea'
WHEN LEFT(rx_sign, 2) = 'R2' THEN 'Kaliningrado'
WHEN LEFT(rx_sign, 2) IN ('R8','R9','R0') THEN 'Rusia Asiática'
WHEN LEFT(rx_sign, 2) IN ('UA1','UA3','UA4','UA5','UA6','UA7','RA1','RA3','RA4','RA5','RA6','RA7') THEN 'Rusia Europea'
WHEN LEFT(rx_sign, 3) IN ('UA2', 'RA2') THEN 'Kaliningrado'
WHEN LEFT(rx_sign, 2) IN ('UA8','UA9','UA0','RA8','RA9','RA0') THEN 'Rusia Asiática'
WHEN LEFT(rx_sign, 2) IN ('UN','UM','UO','UP','UQ') THEN 'Kazaskan'
WHEN LEFT(rx_sign, 2) = 'S5' THEN 'Eslovenia'
WHEN LEFT(rx_sign, 2) IN ('SA','SB','SC', 'SD', 'SE', 'SF', 'SG', 'SH', 'SI', 'SJ', 'SK', 'SL', 'SM', '7S','8S') THEN 'Suecia'
WHEN LEFT(rx_sign, 2) IN ('SN', 'SO', 'SP','SQ', 'SR') THEN 'Polonia'
WHEN LEFT(rx_sign, 2) IN ('SV', 'SW', 'SX','SY', 'SZ', 'J4') THEN 'Grecia'
WHEN LEFT(rx_sign, 2) IN ('TA', 'TB', 'TC') THEN 'Turquia'
WHEN LEFT(rx_sign, 2) IN ('TI', 'TE') THEN 'Costa Rica'
WHEN LEFT(rx_sign, 2) = 'TF' THEN 'Islandia'
WHEN LEFT(rx_sign, 2) = 'TK' THEN 'Corcega'
WHEN LEFT(rx_sign, 2) IN ('UR','US', 'UT', 'UU', 'UV', 'UW', 'UX', 'UY', 'UZ') THEN 'Ucrania'
WHEN LEFT(rx_sign, 2) IN ('VA', 'VB', 'VC', 'VD', 'VE', 'VF', 'VG','VO','VP','VQ','VR','VS','VT','VU','VV','VX','VY') THEN 'Canada'
WHEN LEFT(rx_sign, 2) IN ('VK', 'AX') THEN 'Australia'
WHEN LEFT(rx_sign, 1) IN ('K', 'N', 'W') THEN 'Estados Unidos'
WHEN LEFT(rx_sign, 2) IN ('AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI' , 'AJ', 'AK') THEN 'Estados Unidos'
WHEN LEFT(rx_sign, 2) = 'YL' THEN 'Letonia'
WHEN LEFT(rx_sign, 2) = 'YM' THEN 'Turquia'
WHEN LEFT(rx_sign, 2) IN ('YO', 'YP', 'YQ', 'YR') THEN 'Rumania'
WHEN LEFT(rx_sign, 2) IN ('YT', 'YU') THEN 'Serbia'
WHEN LEFT(rx_sign, 2) = 'ZA' THEN 'Albania'
WHEN LEFT(rx_sign, 3) = 'ZB2' THEN 'Gibraltar'
WHEN LEFT(rx_sign, 3) = 'ZD7' THEN 'St. Helena'
WHEN LEFT(rx_sign, 2) = 'ZF' THEN 'Caiman'
WHEN LEFT(rx_sign, 3) = 'ZL7' THEN ' Chatham '
WHEN LEFT(rx_sign, 3) = 'ZL8' THEN 'Kermadec'
WHEN LEFT(rx_sign, 3) = 'ZL9' THEN 'New Zealand Subantarctic Islands '
WHEN LEFT(rx_sign, 2) IN ('ZL', 'ZM') THEN 'Nueva Zelanda'
WHEN LEFT(rx_sign, 2) = 'ZP' THEN 'Paraguay'
WHEN LEFT(rx_sign, 2) IN ('ZR','ZS','ZT','ZU') THEN 'Sudafrica'
ELSE 'ZZ - Otros'
END AS pais,
COUNT(DISTINCT rx_sign) AS cantidad
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-05-23'
AND time < NOW()
GROUP BY pais
ORDER BY pais ASC;

Ejemplo real de salida con un total de 61 territorios DXCC ARRL

row pais cantidad
1 Alemania 281
2 Australia 17
3 Austria 28
4 Belgica 20
5 Bielorusia 2
6 Bosnia-Herzegovina 3
7 Brasil 6
8 Bulgaria 1
9 Caiman 1
10 Canada 32
11 Chequia 12
12 China 2
13 Chipre 1
14 Colombia 1
15 Costa Rica 1
16 Croacia 5
17 Dinamarca 14
18 Escocia 34
19 Eslovaquia 1
20 Eslovenia 6
21 España - Baleares 1
22 España - Canarias 8
23 España - Península 22
24 Estados Unidos 373
25 Estonia 2
26 Filipinas 1
27 Finlandia 20
28 Francia 77
29 Gales 20
30 Gibraltar 1
31 Grecia 27
32 Guersey 1
33 Hungria 10
34 Inglaterra 286
35 Irlanda 5
36 Irlanda del Norte 6
37 Isla Jersey 1
38 Islandia 7
39 Israel 3
40 Italia 59
41 Japon 1
42 Kaliningrado 2
43 Kazaskan 1
44 Letonia 2
45 Luxemburgo 3
46 Malta 1
47 Namibia 1
48 Noruega 17
49 Nueva Zelanda 4
50 Paises Bajos 129
51 Polonia 19
52 Portugal 9
53 Rumania 3
54 Rusia Asiática 1
55 Rusia Europea 1
56 Serbia 2
57 St. Helena 1
58 Suecia 34
59 Suiza 35
60 Turquia 2
61 Ucrania 2
62 ZZ - Otros 23

Algunos paises mas codificados por si quiere hacer una segunda pasada con ellos

SELECT
CASE
WHEN LEFT(rx_sign, 3) IN ('3B8') THEN 'Mauricio'
WHEN LEFT(rx_sign, 3) IN ('3C0') THEN 'Anobon'
WHEN LEFT(rx_sign, 2) IN ('3C') THEN 'Guinea Ecuatorial'
WHEN LEFT(rx_sign, 2) IN ('3V') THEN 'Tunez'
WHEN LEFT(rx_sign, 2) IN ('3W','XV') THEN 'Vietnam'
WHEN LEFT(rx_sign, 2) = '3X' THEN 'Guinea'
WHEN LEFT(rx_sign, 2) IN ('3O','47') THEN 'Montenegro'
WHEN LEFT(rx_sign, 2) IN ('4J','4K') THEN 'Azerbaijan'
WHEN rx_sign REGEXP '^4U[0-9]ITU$' THEN 'ONU (ITU)'
WHEN rx_sign REGEXP '^4U[0-9]UN$' THEN 'ONU (HQ)'
WHEN LEFT(rx_sign, 2) = '4L' THEN 'Georgia'
WHEN LEFT(rx_sign, 2) = '4S' THEN 'SriLanka'
WHEN LEFT(rx_sign, 2) = '5A' THEN 'Libia'
WHEN LEFT(rx_sign, 2) = '5N' THEN 'Nigeria'
WHEN LEFT(rx_sign, 2) = '5R' THEN 'Mauritania'
WHEN LEFT(rx_sign, 2) = '5T' THEN 'Madagascar'
WHEN LEFT(rx_sign, 2) = '5U' THEN 'Niger'
WHEN LEFT(rx_sign, 2) = '5V' THEN 'Togo'
WHEN LEFT(rx_sign, 2) = '5W' THEN 'Samoa'
WHEN LEFT(rx_sign, 2) = '5X' THEN 'Uganda'
WHEN LEFT(rx_sign, 2) IN ('5Y','5Z') THEN 'Kenia'
WHEN LEFT(rx_sign, 2) IN ('6V','6W') THEN 'Senegal'
WHEN LEFT(rx_sign, 2) = '6Y' THEN 'Jamaica'
WHEN LEFT(rx_sign, 2) = '9N' THEN 'Nepal'
WHEN LEFT(rx_sign, 2) IN ('9Q','9R','9S','9T') THEN 'R. D.Congo'
WHEN LEFT(rx_sign, 2) = '9U' THEN 'Burundi'
WHEN LEFT(rx_sign, 2) = 'E3' THEN 'Eritrea'
WHEN LEFT(rx_sign, 2) = 'E4' THEN 'Palestina'
WHEN LEFT(rx_sign, 2) = 'ET' THEN 'Etiopia'
WHEN LEFT(rx_sign, 2) = 'EX' THEN 'Kyrgyzstan'
WHEN LEFT(rx_sign, 2) = 'EY' THEN 'Tajikistan'
WHEN LEFT(rx_sign, 2) = 'EZ' THEN 'Turkmenistan'
WHEN LEFT(rx_sign, 2) = 'E6' THEN 'Niue'
WHEN LEFT(rx_sign, 2) IN ('EP', 'EQ') THEN 'Iran'
WHEN LEFT(rx_sign, 2) IN ('UJ','UK','UL','UM') THEN 'Uzbekistan'
WHEN LEFT(rx_sign, 2) = 'S2' THEN 'Bangladesh'
WHEN LEFT(rx_sign, 2) = 'EK' THEN 'Armenia'
WHEN LEFT(rx_sign, 2) = 'EL' THEN 'Liberia'
WHEN LEFT(rx_sign, 2) = 'ER' THEN 'Moldavia'
WHEN LEFT(rx_sign, 2) = 'S7' THEN 'Seychelles'
WHEN LEFT(rx_sign, 2) = 'S9' THEN 'Sao Tome & Principe'
WHEN LEFT(rx_sign, 2) = 'V2' THEN 'Antigua'
WHEN LEFT(rx_sign, 2) = 'V3' THEN 'Belice'
WHEN LEFT(rx_sign, 2) = 'V4' THEN 'St. Kitts & Nevis'
WHEN LEFT(rx_sign, 2) IN ('RU','RL') THEN 'Rusia Europea'
ELSE 'ZZ - Otros'
END AS pais,
COUNT(DISTINCT rx_sign) AS cantidad
FROM wspr.rx
WHERE tx_sign = 'EA5JTT'
AND time >= '2025-05-23'
AND time < NOW()
GROUP BY pais
ORDER BY pais ASC;

Estos son algunos ejemplos de cómo explotar la base de datos de WSPRnet, pero con su imaginación y sabiduría llegará donde se proponga

SONOTRIGGER SOFTWARE

Consultas personalizadas de WSPRnet: TX

No hay comentarios:

Publicar un comentario

Baliza V16 de emergencia geolocalizable: CH-020L de Raykong