SONOTRIGGER SOFTWARE: Consultas personalizadas de WSPRnet: RX

Ver: Consultas personalizadas de WSPRnet: TX

Vamos a ver ahora diversas consultas SQL de la BD WSPRnet en WSPR Rocks! desde el punto de vista de la recepción ("spotters").

Hay una serie de consultas preestablecidas para obtener los Top Spotters en https://wspr.rocks/topspotters/

CONSULTAS SQL (QUERYS O SELECTS)

Si se quiere conocer el número total de estaciones WSPR RX (rx-sign) que han recibido spots o beacons en un día en concreto podemos hacer la siguiente consulta:

SELECT COUNT(DISTINCT rx_sign)
FROM wspr.rx
WHERE time > '2025-06-26' AND time < '2025-06-27'

El día 26-6-2025 hubo 1.470 estaciones WSPR RX.

Si lo que queremos conocer es el número total de spots recibidos por el conjunto de la red WSPR en un periodo de tiempo, por ejemplo un día.

SELECT COUNT(*)
FROM wspr.rx
WHERE time > '2025-07-05' AND time < '2025-07-06'

El día 5-7-2025 se recibieron un total de 5.378.012

Para conocer los spots recibidos en un intervalo de tiempo menor, por ejemplo 5' podemos usar

SELECT COUNT(DISTINCT rx_sign)
FROM wspr.rx
WHERE time > '2025-06-27 00:00.00' AND time < '2025-06-27 00:05:00'

En los 5 primero minutos del día 27-6-2025 vemos que hay casi 700 estaciones WSPR RX (Si se consultan 5' que incluye 2 tramas en otro momento de día se conserva el valor), si vemos la totalidad del día el resultado es de 1.470, por lo que podemos pensar que el 50% de las estaciones son permanentes y el resto intermitentes o esporádicas

Spots recibidos desde el inicio del día en el que estamos hasta el momento de la consulta

SELECT COUNT(DISTINCT rx_sign)
FROM wspr.rx
WHERE time > CURDATE() AND time < NOW()

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

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

Si quitamos la sentencia "LIMIT 10" nos saldrian la totalidad de estacioens recibidas que en este caso era de 661

¿De cuantas estaciones WSPR TX distintas hemos recibido spots en un intervalo de tiempo?

SELECT COUNT(DISTINCT tx_sign) AS unique_tx_count
FROM wspr.rx
WHERE rx_sign = 'EA5JTT'
AND time > '2025-05-01'
AND time < NOW();

Si se quiere conocer cuantas estaciones han estado recibiendo en EA(España) en lo que va de jornada usaríamos la siguiente consulta:

SELECT rx_sign, COUNT(*)
FROM wspr.rx
WHERE rx_sign LIKE 'EA%' OR rx_sign LIKE 'EB%' AND
time > CURDATE() AND time < NOW()
GROUP BY rx_sign
ORDER BY COUNT(*) DESC;

El numero de WSPR RX en España es escaso y hay distritos que o no tienen o su presencia es testimonial para la superficie que ocupan

Para varios prefijos es mejor usar LEFT que OR

WHERE LEFT(rx_sign, 2) IN ('EA', 'EB', 'EC')

quedando de la siguiente manera

SELECT rx_sign, COUNT(*) AS total
FROM wspr.rx
WHERE LEFT(rx_sign, 2) IN ('EA', 'EB', 'EC')
AND time > '2025-07-26' AND time < '2025-07-27'
GROUP BY rx_sign
ORDER BY total DESC;

Listado de las estaciones WSPR TX que comienzan por E, F y G

SELECT tx_sign, COUNT(*)
FROM wspr.rx
WHERE match(tx_sign,'^[EFG]') = 1
AND time > CURDATE() AND time < NOW()
GROUP BY tx_sign
ORDER BY COUNT(*) DESC;

También podemos preguntar sobre el número de spots que recibe cada WSPR RX para un intervalo de fechas o tiempos, al estar ordenado si sabe también la posición que se ocupa por el número de spots recibidos:

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

El número de estaciones WSPR RX que reciben spots  en un día como el 13/6/2025 fue de 1.512, la media es de 2.356spots/estación con un máximo de 44.330.

Hay unas 4 veces más de estaciones WSPR TX que de WSPR RX.

Si queremos saber los que se han recibido en lo que llevamos de día

SELECT rx_sign, COUNT(*)
FROM wspr.rx
WHERE time > CURDATE() AND time < NOW()
GROUP BY rx_sign
ORDER BY COUNT(*) DESC;

Si queremos saber únicamente los que ha recibido una estación en concreto en lo que va de día podemos hacer:

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

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

SELECT *
FROM wspr.rx
WHERE rx_sign='EA5JTT' AND time > '2025-06-27 20:00:00' AND time < '2025-06-27 21:00:00'
ORDER BY time;

El listado de registros pero solamente de las estaciones que están a mas de 12.000 km

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

Mas aseado

SELECT id,
formatDateTime(time, '%d/%m/%Y') AS FECHA,
replace(formatDateTime(time, '%R'), ':', '') AS HORA,
replace(toString(frequency/1000000), '.', ',') AS f_MHz,
'WSPR' AS Mode,
tx_sign AS TX,
power AS pwr_dBm,
rx_sign AS RX,
distance AS d_km,
rx_sign AS RX,
snr AS SNR_dB
FROM wspr.rx
WHERE rx_sign='EA5JTT' AND time > '2025-06-14 03:00:00' AND time <NOW() AND distance > 12000
ORDER BY distance DESC;

El listado anterior ordenado por tiempo y descartando duplicados, para el día de hoy hasta ahora quedaría de la siguiente manera:

SELECT DISTINCT tx_sign
FROM wspr.rx
WHERE rx_sign='EA5JTT' AND time > CURDATE() AND time < NOW()
ORDER BY HOUR(time);

Listado de estaciones diferentes recibidas (879 hasta el 9/8/2025 con OpenWSPR de Remote QTH)

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

El listado de  estaciones transmisoras únicas con el numero de spots emitidos por cada una que ha recibido la WSPR RX (EA5JTT)

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

Registros recibidos en la banda 3 (3,5 MHz, 80m)

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


Numero de spots recibidos, de estaciones diferentes, en la ultima hora y en que bandas

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

Lo mismo pero en la última media hora

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

Veces que la estación WSPR RX EA5JTT ha recibido a la WSPR TX EA6URP

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

Ahora se van a obtener todos los registros recibidos para analizar los patrones de propagación entre Valencia y Mallorca, o entre la TX y RX de EA5JTT.

SELECT time, band, tx_sign, rx_sign, power, snr, distance FROM wspr.rx 
WHERE rx_sign='EA5JTT' AND tx_sign='EA6URP' AND time > '2025-06-25' AND time < NOW() 
ORDER BY time;

SELECT time, band, tx_sign, rx_sign, power, snr, distance 
FROM wspr.rx 
WHERE rx_sign='EA5JTT' AND tx_sign='EA5JTT' AND time > '2025-06-25' AND time < NOW() 
ORDER BY time;

Estaciones receptoras de WSPR en una cuadricula determinada (Con 4 caracteres están las que transmiten con 4 caracteres y las de que transmiten con 6)

SELECT rx_loc, COUNT(*)
FROM wspr.rx
WHERE rx_loc LIKE 'IM99%' AND time > '2025-06-13' AND time < '2025-06-25'
GROUP BY rx_loc
ORDER BY COUNT(*) 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 rx_sign='EA5JTT' AND time > '2025-05-27' AND time < '2025-06-29'
GROUP BY band;

Detalle de los spots recibidos por una estación WSPR RX en un intervalo de tiempo agrupado por bandas y por días

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 rx_sign = 'EA5JTT' AND time >= '2025-06-26' 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 rx_sign = 'EA5JTT' AND time >= '2025-06-26' AND time < NOW()
ORDER BY fecha;

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

SELECT HOUR(time), band, COUNT(*)
FROM wspr.rx
WHERE rx_sign='EA5JTT' AND time > '2025-05-27' AND time < '2025-06-28'
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 rx_sign = 'EA5JTT' AND time > '2025-06-27' AND time < '2025-06-28'
GROUP BY HOUR(time)
ORDER BY HOUR(time);

mas completo

SELECT
toString(HOUR(time)) AS Hora,
COUNT(CASE WHEN band = '-1' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_LF,
COUNT(CASE WHEN band = '0' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_MF,
COUNT(CASE WHEN band = '1' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_1x,
COUNT(CASE WHEN band = '3' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_3,
COUNT(CASE WHEN band = '5' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_5,
COUNT(CASE WHEN band = '7' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_7,
COUNT(CASE WHEN band = '10' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_10,
COUNT(CASE WHEN band = '14' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_14,
COUNT(CASE WHEN band = '18' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_18,
COUNT(CASE WHEN band = '21' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_21,
COUNT(CASE WHEN band = '24' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_24,
COUNT(CASE WHEN band = '28' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_28,
COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_total
FROM wspr.rx
WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT')
AND time >= CURDATE() AND time < NOW()
GROUP BY HOUR(time)
UNION ALL
SELECT
'TOTAL' AS fecha,
COUNT(CASE WHEN band = '-1' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '0' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '1' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '3' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '5' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '7' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '10' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '14' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '18' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '21' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '24' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '28' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END)
FROM wspr.rx
WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT')
AND time >= CURDATE() AND time < NOW()
ORDER BY
CASE WHEN fecha = 'TOTAL' THEN 1 ELSE 0 END,
fecha;

Completo por dias y bandas

SELECT
toString(DATE(time)) AS fecha,
COUNT(CASE WHEN band = '-1' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_LF,
COUNT(CASE WHEN band = '0' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_MF,
COUNT(CASE WHEN band = '1' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_1x,
COUNT(CASE WHEN band = '3' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_3,
COUNT(CASE WHEN band = '5' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_5,
COUNT(CASE WHEN band = '7' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_7,
COUNT(CASE WHEN band = '10' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_10,
COUNT(CASE WHEN band = '14' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_14,
COUNT(CASE WHEN band = '18' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_18,
COUNT(CASE WHEN band = '21' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_21,
COUNT(CASE WHEN band = '24' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_24,
COUNT(CASE WHEN band = '28' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_28,
COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_total
FROM wspr.rx
WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT')
AND time >= '2025-06-27' AND time < NOW()
GROUP BY DATE(time)
UNION ALL
SELECT
'TOTAL' AS fecha,
COUNT(CASE WHEN band = '-1' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '0' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '1' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '3' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '5' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '7' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '10' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '14' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '18' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '21' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '24' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band = '28' AND rx_sign = 'EA5JTT' THEN 1 END),
COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END)
FROM wspr.rx
WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT')
AND time >= '2025-06-27' AND time < NOW()
ORDER BY
CASE WHEN fecha = 'TOTAL' THEN 1 ELSE 0 END,
fecha;

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 rx_sign = 'EA5JTT' AND time > '2025-06-27 00:00:00' AND time < '2025-06-28 00:00:00'
GROUP BY MINUTE(time)
ORDER BY MINUTE(time);

Optimizado para Clikhouse el motor de BD de WSPRnet

SELECT
formatDateTime(time, '%R') AS HoraMinuto,
countIf(band = '3') AS "80m",
countIf(band = '5') AS "60m",
countIf(band = '7') AS "40m",
countIf(band = '10') AS "30m",
countIf(band = '14') AS "20m",
countIf(band = '18') AS "17m",
countIf(band = '21') AS "15m",
countIf(band = '24') AS "12m",
countIf(band = '28') AS "10m",
countIf(band != '99') AS TOTAL
FROM wspr.rx
WHERE rx_sign = 'EA5JTT'
AND time >= today()
AND time < now()
GROUP BY HoraMinuto
ORDER BY HoraMinuto;

Vamos a hora a obtener las distancias máximas TX-RX para un dia concreto 27-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 ven 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 rx_sign = 'EA5JTT'
AND time >= '2025-06-27' AND time < '2025-06-28'
GROUP BY HOUR(time) ORDER BY HOUR(time);

DIstancia máxima por días

SELECT  toString(DATE(time)) AS fecha, 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  rx_sign = 'EA5JTT'  AND time >= '2025-06-27'   AND time < NOW() 
GROUP BY  fecha 
ORDER BY  fecha;

Puede ser interesante conocer las mínimas potencias recibidas para un dia concreto 27-6-2025, por horas y bandas.

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

También puede interesarnos las mínimas o máximas S/N medidas para un día concreto 28-6-2025, por horas y bandas.
SELECT HOUR(time) AS hora, MIN(CASE WHEN band = '3' THEN snr END) AS "80m", MIN(CASE WHEN band = '7' THEN snr END) AS "40m", MIN(CASE WHEN band = '10' THEN snr END) AS "30m",  MIN(CASE WHEN band = '14' THEN snr END) AS "20m", MIN(CASE WHEN band = '18' THEN snr END) AS "17m", MIN(CASE WHEN band = '21' THEN snr END) AS "15m", MIN(CASE WHEN band = '24' THEN snr END) AS "12m",  MIN(CASE WHEN band = '28' THEN snr END) AS "10m" 
FROM  wspr.rx 
WHERE  rx_sign = 'EA5JTT'  AND time >= '2025-06-28'   AND time < '2025-06-29' GROUP BY  HOUR(time) ORDER BY  HOUR(time);
SELECT HOUR(time) AS hora, MAX(CASE WHEN band = '3' THEN snr END) AS "80m", MAX(CASE WHEN band = '7' THEN snr END) AS "40m", MAX(CASE WHEN band = '10' THEN snr END) AS "30m",  MAX(CASE WHEN band = '14' THEN snr END) AS "20m", MAX(CASE WHEN band = '18' THEN snr END) AS "17m", MAX(CASE WHEN band = '21' THEN snr END) AS "15m", MAX(CASE WHEN band = '24' THEN snr END) AS "12m",  MAX(CASE WHEN band = '28' THEN snr END) AS "10m" 
FROM  wspr.rx 
WHERE  rx_sign = 'EA5JTT'  AND time >= '2025-06-28'   AND time < '2025-06-29' 
GROUP BY  HOUR(time) 
ORDER BY  HOUR(time);

También el promedio redondeado a 1 decimal

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

Valor absoluto de recepciones 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 rx_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 rx_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 rx_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;

Distancia de donde proceden los spots recibidos, agrupadas

SELECT COALESCE(band, 'TOTAL') AS banda, COUNT(CASE WHEN distance < 7500 THEN 1 END) AS m_750, COUNT(CASE WHEN distance >= 750 AND distance < 1500 THEN 1 END) AS d_750_1500, COUNT(CASE WHEN distance >= 1500 AND distance < 3000 THEN 1 END) AS d_1500_3000, COUNT(CASE WHEN distance >= 3000 AND distance < 6000 THEN 1 END) AS d_3000_6000, COUNT(CASE WHEN distance >= 6000 THEN 1 END) AS M_6000, COUNT(*) AS total_banda
FROM wspr.rx
WHERE rx_sign = 'EA5JTT' AND time > CURDATE() AND time < NOW()
GROUP BY band WITH ROLLUP;

Potencia de las emisoras de las que se reciben spots

SELECT COALESCE(band, 'TOTAL') AS banda, COUNT(CASE WHEN power < 17 THEN 1 END) AS m_17dBm_50mW, COUNT(CASE WHEN power >= 17 AND power <23 THEN 1 END) AS 17_23dBm_200mW, COUNT(CASE WHEN power = 23 THEN 1 END) AS 23dBm_200mW, COUNT(CASE WHEN power > 23 AND power <= 30 THEN 1 END) AS 23_30dBm_1W, COUNT(CASE WHEN power > 30 THEN 1 END) AS M_30dBm_1W,COUNT(*) AS total_banda
FROM wspr.rx
WHERE rx_sign = 'EA5JTT' AND time > CURDATE() AND time < NOW()
GROUP BY band WITH ROLLUP;

Comprobar un informe de recepción recibido, y obtener datos para generar una eQSL

SELECT
id AS WSPRnet_id,
formatDateTime(time, '%d/%m/%Y') AS DATE,
replace(formatDateTime(time, '%R'), ':', '') AS TIME,
replace(toString(frequency/1000000), '.', ',') AS f_MHz,
'WSPR' AS Mode,
tx_sign AS Callsign,
power AS pwr_dBm,
snr AS SNR_dB,
distance AS d_km
FROM wspr.rx
WHERE tx_sign='EA5JTT' AND rx_sign='PA11674SWL' AND
band='10' AND
time > '2025-07-30 23:25:00' AND time < '2025-07-30 23:45:00'
ORDER BY time;

Listado de los spots recibidos por estaciones con indicativo de escucha SWL que han recibido a EA5JTT

SELECT
id AS WSPRnet_id,
formatDateTime(time, '%d/%m/%Y') AS DATE,
replace(formatDateTime(time, '%R'), ':', '') AS TIME,
replace(toString(frequency/1000000), '.', ',') AS f_MHz,
'WSPR' AS Mode,
rx_sign AS Callsign,
power AS pwr_dBm,
snr AS SNR_dB,
distance AS d_km
FROM wspr.rx
WHERE rx_sign LIKE '%SWL%'
AND tx_sign = 'EA5JTT'
AND time > '2025-05-15' AND time < NOW()
ORDER BY Callsign;

Listado de las estaciones SWL     ue han recibido los spots de EA5JTT ordenados por la distancia a la que se encuentran, con la indicaciones de las veces que lo han recibido

SELECT
rx_sign,
any(distance) AS distance,
COUNT(*) AS total
FROM wspr.rx
WHERE rx_sign LIKE '%SWL%'
AND tx_sign = 'EA5JTT'
AND time > '2025-05-15'
AND time < NOW()
GROUP BY rx_sign
ORDER BY distance DESC;

Con todos estos ejemplos tanto para WSPR TX como para RX tiene material para escribir sus propias consultas.

Errores 

No se ejecuta
Hay un error en el SQL, por ejemplo falta cerrar unas comillas, falta una coma, ...
TypeError: Failed to fetch
Normalmente es por falta de Internet/wiFi

SONOTRIGGER SOFTWARE

Consultas personalizadas de WSPRnet: RX

No hay comentarios:

Publicar un comentario

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