TringaliLuca

Calculate t-score and r-score from RikiDolphin data

Aug 31st, 2026
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.90 KB | Science | 0 0
  1. WITH cte1 AS (
  2.    SELECT
  3.       r_temperature,
  4.       d_temperature
  5.    FROM abisso_bonetti
  6.    WHERE unixepoch(STRFTIME('%Y-%m-%dT%H:%M:%SZ', TIME)) > ${__from:DATE:seconds} AND unixepoch(STRFTIME('%Y-%m-%dT%H:%M:%SZ', TIME)) < ${__to:DATE:seconds}
  7.    AND d_temperature < 100 AND r_temperature < 100 AND d_temperature > -100 AND r_temperature > -100
  8. ),
  9. CriticalTValues(Df,p050,p025,p020,p015,p010,p005,p0025,p001,p0005,p0001,p00005) AS (
  10.    SELECT * FROM ( VALUES
  11. (1, 0.000, 1.000, 1.376, 1.963, 3.078, 6.314, 12.71, 31.82, 63.66, 318.31, 636.62),
  12. (2, 0.000, 0.816, 1.061, 1.386, 1.886, 2.920, 4.303, 6.965, 9.925, 22.327, 31.599),
  13. (3, 0.000, 0.765, 0.978, 1.250, 1.638, 2.353, 3.182, 4.541, 5.841, 10.215, 12.924),
  14. (4, 0.000, 0.741, 0.941, 1.190, 1.533, 2.132, 2.776, 3.747, 4.604, 7.173, 8.610),
  15. (5, 0.000, 0.727, 0.920, 1.156, 1.476, 2.015, 2.571, 3.365, 4.032, 5.893, 6.869),
  16. (6, 0.000, 0.718, 0.906, 1.134, 1.440, 1.943, 2.447, 3.143, 3.707, 5.208, 5.959),
  17. (7, 0.000, 0.711, 0.896, 1.119, 1.415, 1.895, 2.365, 2.998, 3.499, 4.785, 5.408),
  18. (8, 0.000, 0.706, 0.889, 1.108, 1.397, 1.860, 2.306, 2.896, 3.355, 4.501, 5.041),
  19. (9, 0.000, 0.703, 0.883, 1.100, 1.383, 1.833, 2.262, 2.821, 3.250, 4.297, 4.781),
  20. (10, 0.000, 0.700, 0.879, 1.093, 1.372, 1.812, 2.228, 2.764, 3.169, 4.144, 4.587),
  21. (11, 0.000, 0.697, 0.876, 1.088, 1.363, 1.796, 2.201, 2.718, 3.106, 4.025, 4.437),
  22. (12, 0.000, 0.695, 0.873, 1.083, 1.356, 1.782, 2.179, 2.681, 3.055, 3.930, 4.318),
  23. (13, 0.000, 0.694, 0.870, 1.079, 1.350, 1.771, 2.160, 2.650, 3.012, 3.852, 4.221),
  24. (14, 0.000, 0.692, 0.868, 1.076, 1.345, 1.761, 2.145, 2.624, 2.977, 3.787, 4.140),
  25. (15, 0.000, 0.691, 0.866, 1.074, 1.341, 1.753, 2.131, 2.602, 2.947, 3.733, 4.073),
  26. (16, 0.000, 0.690, 0.865, 1.071, 1.337, 1.746, 2.120, 2.583, 2.921, 3.686, 4.015),
  27. (17, 0.000, 0.689, 0.863, 1.069, 1.333, 1.740, 2.110, 2.567, 2.898, 3.646, 3.965),
  28. (18, 0.000, 0.688, 0.862, 1.067, 1.330, 1.734, 2.101, 2.552, 2.878, 3.610, 3.922),
  29. (19, 0.000, 0.688, 0.861, 1.066, 1.328, 1.729, 2.093, 2.539, 2.861, 3.579, 3.883),
  30. (20, 0.000, 0.687, 0.860, 1.064, 1.325, 1.725, 2.086, 2.528, 2.845, 3.552, 3.850),
  31. (21, 0.000, 0.686, 0.859, 1.063, 1.323, 1.721, 2.080, 2.518, 2.831, 3.527, 3.819),
  32. (22, 0.000, 0.686, 0.858, 1.061, 1.321, 1.717, 2.074, 2.508, 2.819, 3.505, 3.792),
  33. (23, 0.000, 0.685, 0.858, 1.060, 1.319, 1.714, 2.069, 2.500, 2.807, 3.485, 3.768),
  34. (24, 0.000, 0.685, 0.857, 1.059, 1.318, 1.711, 2.064, 2.492, 2.797, 3.467, 3.745),
  35. (25, 0.000, 0.684, 0.856, 1.058, 1.316, 1.708, 2.060, 2.485, 2.787, 3.450, 3.725),
  36. (26, 0.000, 0.684, 0.856, 1.058, 1.315, 1.706, 2.056, 2.479, 2.779, 3.435, 3.707),
  37. (27, 0.000, 0.684, 0.855, 1.057, 1.314, 1.703, 2.052, 2.473, 2.771, 3.421, 3.690),
  38. (28, 0.000, 0.683, 0.855, 1.056, 1.313, 1.701, 2.048, 2.467, 2.763, 3.408, 3.674),
  39. (29, 0.000, 0.683, 0.854, 1.055, 1.311, 1.699, 2.045, 2.462, 2.756, 3.396, 3.659),
  40. (30, 0.000, 0.683, 0.854, 1.055, 1.310, 1.697, 2.042, 2.457, 2.750, 3.385, 3.646),
  41. (40, 0.000, 0.681, 0.851, 1.050, 1.303, 1.684, 2.021, 2.423, 2.704, 3.307, 3.551),
  42. (60, 0.000, 0.679, 0.848, 1.045, 1.296, 1.671, 2.000, 2.390, 2.660, 3.232, 3.460),
  43. (80, 0.000, 0.678, 0.846, 1.043, 1.292, 1.664, 1.990, 2.374, 2.639, 3.195, 3.416),
  44. (100, 0.000, 0.677, 0.845, 1.042, 1.290, 1.660, 1.984, 2.364, 2.626, 3.174, 3.390),
  45. (1000, 0.000, 0.675, 0.842, 1.037, 1.282, 1.646, 1.962, 2.330, 2.581, 3.098, 3.300),
  46. (10000, 0.000, 0.674, 0.842, 1.036, 1.282, 1.645, 1.960, 2.326, 2.576, 3.090, 3.291)
  47. ))
  48. SELECT
  49.    rvalue,
  50.    deg_free,
  51.    tscore,
  52.    CAST((
  53.       SELECT CASE
  54.       WHEN tscore>p00005 THEN '0.0005'
  55.       WHEN tscore>p0001 THEN '0.001'
  56.       WHEN tscore>p0005 THEN '0.005'
  57.       WHEN tscore>p001 THEN '0.01'
  58.       WHEN tscore>p0025 THEN '0.025'
  59.       WHEN tscore>p005 THEN '0.05'
  60.       WHEN tscore>p010 THEN '0.10'
  61.       WHEN tscore>p015 THEN '0.15'
  62.       WHEN tscore>p020 THEN '0.20'
  63.       WHEN tscore>p025 THEN '0.25'
  64.       WHEN tscore>p050 THEN '0.50'
  65.       ELSE '1'
  66.       END pvalue
  67.       FROM (
  68.          SELECT * FROM CriticalTValues WHERE CriticalTValues.Df >= deg_free ORDER BY Df ASC LIMIT 1
  69.       )
  70.    ) AS DECIMAL)*2 AS pvalue
  71. FROM (
  72.    SELECT
  73.       pearson AS rvalue,
  74.       ((pearson*POWER((_count-2),0.5))/(POWER((1-POWER(pearson,2)),0.5))) AS tscore,
  75.       (_count-2) AS deg_free
  76.    FROM (
  77.       SELECT
  78.          ((tot_sum - (amt_sum * act_sum / _count)) / SQRT((amt_sum_sq - pow(amt_sum, 2.0) / _count) * (act_sum_sq - pow(act_sum, 2.0) / _count))) AS pearson, _count
  79.       FROM(
  80.          SELECT
  81.             SUM(cte1.r_temperature) AS amt_sum,
  82.             SUM(cte1.d_temperature) AS act_sum,
  83.             SUM(POWER(cte1.r_temperature,2)) AS amt_sum_sq,
  84.             SUM(POWER(cte1.d_temperature,2)) AS act_sum_sq,
  85.             SUM(cte1.r_temperature * cte1.d_temperature) AS tot_sum,
  86.             COUNT(*) AS _count
  87.          FROM cte1
  88.       ) AS a
  89.       GROUP BY tot_sum, amt_sum, act_sum, _count, amt_sum_sq, act_sum_sq
  90.    ) AS b
  91. ) AS c;
  92.  
Advertisement
Add Comment
Please, Sign In to add comment