Consultas personalizadas de WSPRnet: TX y RX

Si se quieren ver los registros completos (SELECT *) de un intervalo, por ejemplo desde las 00h00 a las 01h00 en las que EA5JTT recibe tramas o las suyas son recibidas por otras estaciones haríamos:

• SELECT * FROM wspr.rx 
• WHERE (rx_sign='EA5JTT' OR tx_sign='EA5JTT') 
• AND time > '2025-07-09 00:00:00' 
• AND time < '2025-07-09 01:00:00' 
• ORDER BY time;

• SELECT * FROM wspr.rx 
• WHERE (rx_sign='EA5JTT' OR tx_sign='EA5JTT') 
• AND (rx_sign='LA1ITS' OR tx_sign='LA1ITS') 
• AND time > '2025-07-09 00:00:00' AND time < '2025-07-09 01:00:00' 
• ORDER BY time;

Un poco mas elaborado es la siguiente Query que intenta encontrar las parejas o QSO en WSPR establecidos en el dia 9 de las 00 a las 01h con un intervalo de 10 minutos (da error por tiempo, pero se puede afinar)

• SELECT  
• tx.time AS tx_time, tx.tx_sign AS tx_sender,  
• tx.rx_sign AS tx_receiver, rx.time AS rx_time, 
• rx.tx_sign AS rx_sender, rx.rx_sign AS rx_receiver 
• FROM wspr.rx tx 
• INNER JOIN wspr.rx rx   ON tx.tx_sign = rx.rx_sign   AND 
• tx.rx_sign = rx.tx_sign AND 
• abs(toUnixTimestamp(tx.time) - toUnixTimestamp(rx.time)) <= 600 
• WHERE   tx.time > '2025-07-09 00:00:00'  AND 
• tx.time < '2025-07-09 01:00:00' AND 
• rx.time >= '2025-07-09 00:00:00' AND 
• rx.time < '2025-07-09 01:00:00';

Concretando para nuestra estación EA5JTT para un log o generara QSL's

• SELECT 
• tx.id AS id_TX, 
• tx.time AS time_TX, 
• tx.band AS band_TX, 
• tx.frequency AS f_TX_Hz, 
• tx.tx_sign AS Callsign_TX, 
• tx.power AS power_TX, 
• tx.rx_sign AS Callsign_RX, 
• tx.snr AS SNR_RX, 
• rx.id AS id_rx, 
• rx.time AS time_rx, 
• rx.band AS band_rx, 
• rx.frequency AS f_rx_Hz, 
• rx.tx_sign AS Callsign_tx , 
• rx.power AS power_rx,  
• rx.rx_sign AS Callsign_rx ,  
• rx.snr AS SNR_rx 
• FROM wspr.rx tx 
• INNER JOIN 
• wspr.rx rx  ON tx.tx_sign = rx.rx_sign  AND 
• tx.rx_sign = rx.tx_sign AND 
• abs(toUnixTimestamp(tx.time) - toUnixTimestamp(rx.time)) <= 300 
• WHERE    
• tx.time > '2025-07-09'    AND 
• tx.time < '2025-07-10'   AND 
• rx.time >= '2025-07-09' AND 
• rx.time < '2025-07-10' AND 
• tx.tx_sign ='EA5JTT';

Si queremos excluir algunos indicativos que ya tenemos informados o con QSL podemos añadir una lista con ellos "tx.rx_sign NOT IN ('G4LEN','DF1VB' )"

• SELECT tx.id AS id_TX, 
• formatDateTime(time, '%d/%m/%Y') AS date_TX, 
• formatDateTime(tx.time, '%R') AS time_TX, 
• tx.band AS band_TX, 
• replace(toString(tx.frequency/1000000), '.', ',') AS f_TX_MHz, 
• tx.tx_sign AS Callsign_TX, 
• tx.power AS power_TX, 
• tx.rx_sign AS Callsign_RX, 
• tx.snr AS SNR_RX, 
• rx.id AS id_rx, 
• formatDateTime(rx.time, '%d/%m/%Y') AS dte_RX, 
• formatDateTime(rx.time, '%R') AS time_RX, 
• rx.band AS band_rx, 
• replace(toString(rx.frequency/1000000), '.', ',') AS f_RX_MHz, 
• rx.tx_sign AS Callsign_tx , 
• rx.power AS power_rx,  
• rx.rx_sign AS Callsign_rx ,  
• tx.snr AS SNR_rx 
• FROM wspr.rx tx 
• INNER JOIN wspr.rx rx  
• ON tx.tx_sign = rx.rx_sign  AND tx.rx_sign = rx.tx_sign 
• AND abs(toUnixTimestamp(tx.time) - toUnixTimestamp(rx.time)) <= 1800 
• WHERE  tx.time BETWEEN '2025-07-09' AND NOW()
• AND rx.time BETWEEN '2025-07-09' AND NOW() AND 
• tx.tx_sign ='EA5JTT' AND 
• tx.rx_sign NOT IN (
• 'G4LEN','DF1VB') 
• ORDER BY  tx.time ASC;

Si quisieramos contabilizar de forma conjunta los mensajes de TX y RX podriamos hacer

• 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' OR tx_sign = 'EA5JTT') 
•   AND time >= '2025-05-01' 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' OR tx_sign = 'EA5JTT') 
•   AND time >= '2025-05-01' AND time < NOW()
• 
  
• ORDER BY 
•     CASE WHEN fecha = 'TOTAL' THEN 1 ELSE 0 END,
•     fecha;

Si quisieramos tener en columnas separadas TX y RX

• SELECT 
•     toString(DATE(time)) AS fecha, 
• 
  
•     COUNT(CASE WHEN band = '3'  AND rx_sign = 'EA5JTT' THEN 1 END)  AS rx_band_3, 
•     COUNT(CASE WHEN band = '7'  AND rx_sign = 'EA5JTT' THEN 1 END)  AS rx_band_7, 
•     COUNT(CASE WHEN band = '10' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_band_10, 
•     COUNT(CASE WHEN band = '14' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_band_14, 
•     COUNT(CASE WHEN band = '18' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_band_18, 
•     COUNT(CASE WHEN band = '21' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_band_21, 
•     COUNT(CASE WHEN band = '24' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_band_24, 
•     COUNT(CASE WHEN band = '28' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_band_28, 
• 
  
•     COUNT(CASE WHEN band = '3'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_band_3, 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_band_7, 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_band_10, 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_band_14, 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_band_18, 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_band_21, 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_band_24, 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_band_28, 
• 
  
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_total,
•     COUNT(CASE WHEN band <> '99' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_total
• 
  
• FROM wspr.rx 
• WHERE (rx_sign = 'EA5JTT' OR tx_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'  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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END), 
• 
  
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band <> '99' AND tx_sign = 'EA5JTT' THEN 1 END)
• 
  
• FROM wspr.rx 
• WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT') 
•   AND time >= '2025-06-26' AND time < NOW()
• 
  
• ORDER BY 
•     CASE WHEN fecha = 'TOTAL' THEN 1 ELSE 0 END,
•     fecha;

PAra ver el detalle por horas

• SELECT 
•     toString(HOUR(time)) AS Hora, 
•     COUNT(CASE WHEN band = '3'  AND rx_sign = 'EA5JTT' THEN 1 END)  AS rx_3, 
•     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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_3, 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_7, 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_10, 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_14, 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_18, 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_21, 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx__24, 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_28, 
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_total,
•     COUNT(CASE WHEN band <> '99' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_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 = '3'  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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band <> '99' AND tx_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;

Banda RX completa

• 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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_3, 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_7, 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_10, 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_14, 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_18, 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_21, 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx__24, 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_28, 
• 
  
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_total,
•     COUNT(CASE WHEN band <> '99' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END), 
• 
  
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band <> '99' AND tx_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;

Banda RX completa

• 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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_3, 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END)  AS tx_7, 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_10, 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_14, 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_18, 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_21, 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx__24, 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_28, 
• 
  
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END) AS rx_total,
•     COUNT(CASE WHEN band <> '99' AND tx_sign = 'EA5JTT' THEN 1 END) AS tx_total
• 
  
• FROM wspr.rx 
• WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT') 
•   AND time >= '2025-07-26' 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 = '3'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '7'  AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '10' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '14' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '18' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '21' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '24' AND tx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band = '28' AND tx_sign = 'EA5JTT' THEN 1 END), 
• 
  
•     COUNT(CASE WHEN band <> '99' AND rx_sign = 'EA5JTT' THEN 1 END), 
•     COUNT(CASE WHEN band <> '99' AND tx_sign = 'EA5JTT' THEN 1 END)
• 
  
• FROM wspr.rx 
• WHERE (rx_sign = 'EA5JTT' OR tx_sign = 'EA5JTT') 
•   AND time >= '2025-07-26' AND time < NOW()
• 
  
• ORDER BY 
•     CASE WHEN fecha = 'TOTAL' THEN 1 ELSE 0 END,
•     fecha;

Para obtener las estaciones TX y RX de España haríamos

• SELECT 'TX' AS tipo, tx_sign AS indicativo, COUNT(*) AS total
• FROM wspr.rx 
• WHERE LEFT(tx_sign, 2) IN ('EA', 'EB', 'EC')
•   AND time > '2025-07-26' AND time < '2025-07-27'
• GROUP BY tx_sign
• 
  
• ORDER BY total DESC
• 
  
• UNION ALL
• 
  
• SELECT 'RX' AS tipo, rx_sign AS indicativo, 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;

Total 

• SELECT 
•   indicativo,
•   SUM(total) AS total_combinado
• FROM (
•   SELECT tx_sign AS indicativo, COUNT(*) AS total
•   FROM wspr.rx 
•   WHERE LEFT(tx_sign, 2) IN ('EA', 'EB', 'EC')
•     AND time > '2025-07-26' AND time < '2025-07-27'
•   GROUP BY tx_sign
• 
  
•   UNION ALL
• 
  
•   SELECT rx_sign AS indicativo, 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
• ) AS totales
• GROUP BY indicativo
• ORDER BY total_combinado DESC;

Optimizada para ClickHouse que es el motor de WSPR Rocks segun ChatGPT

• SELECT 
•   indicativo,
•   SUM(total) AS total_combinado
• FROM (
•   SELECT tx_sign AS indicativo, count() AS total
•   FROM wspr.rx 
•   WHERE (startsWith(tx_sign, 'EA') OR startsWith(tx_sign, 'EB') OR startsWith(tx_sign, 'EC'))
•     AND time >= toDateTime('2025-07-26 00:00:00') AND time < toDateTime('2025-07-27 00:00:00')
•   GROUP BY tx_sign
• 
  
•   UNION ALL
• 
  
•   SELECT rx_sign AS indicativo, count() AS total
•   FROM wspr.rx 
•   WHERE (startsWith(rx_sign, 'EA') OR startsWith(rx_sign, 'EB') OR startsWith(rx_sign, 'EC'))
•     AND time >= toDateTime('2025-07-26 00:00:00') AND time < toDateTime('2025-07-27 00:00:00')
•   GROUP BY rx_sign
• ) AS totales
• GROUP BY indicativo
• ORDER BY total_combinado DESC;

Si recibimos una QSL o eQSL es muy fácil comprobar los datos si se incluye el id WSPRnet (Yo lo incluyo siempre)

• SELECT *
• FROM wspr.rx 
• WHERE id='10442806972';

o en el caso de un 2-way 

• SELECT *
• FROM wspr.rx 
• WHERE id='10442806972' OR id='10442867076';

Cuando tiene operativos TX y RX que se van alternando en la operación puede querer consultalo con este SELECT

• SELECT 
•   formatDateTime(time, '%R') AS HoraMinuto,
•   'TX' AS tipo,
•   countIf(band = '1')  AS "160m", 
•   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 tx_sign = 'EA5JTT' 
•   AND time >= today() 
•   AND time < now()
• GROUP BY HoraMinuto, tipo
• ORDER BY HoraMinuto DESC, tipo
• 
  
• UNION ALL
• 
  
• SELECT 
•   formatDateTime(time, '%R') AS HoraMinuto,
•   'RX' AS tipo,
•   countIf(band = '1')  AS "160m", 
•   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, tipo
• ORDER BY HoraMinuto DESC, tipo;

COMENTARIOS

A WSPR ROCKS no le gustan los comentarios en SQL (lines que comienzan por --)

En wsprnet no se almacenan registros en los que tx_sign = rx_sign

Prohibida la reproducción parcial o total de este artículo sin permiso previo del autor