SQL 计数值大于列集阈值的行

Question

我有一个包含两个索引列（time_secs和session）和 20metric_*个浮点值列的表。这里有一个小样本：sqlite> SELECT time_secs, session, metric_1, metric_1_likelihood, metric_2, metric_2_likelihood FROM full_ts LIMIT 10;time_secs   session     metric_1            metric_1_likelihood  metric_2           metric_2_likelihood----------  ----------  ------------------  -------------------  -----------------  -------------------1572522600  session_0   0.0698785211320749  0.424853512805612    0.494699869796956  0.962915383990865  1572522900  session_0   0.760571893135022   0.294675049990505    0.580396389327591  0.934424555702509  1572523200  session_0   0.629603845787639   0.806491332113479    0.16589472228813   0.963243379341578  1572523500  session_0   0.0571856086488497  0.605895821260498    0.910067195100557  0.480348834963859  1572523800  session_0   0.840306423849594   0.54489589788877     0.352571588765252  0.160847342139792  1572524100  session_0   0.681384243040651   0.528022904478869    0.047929896649995  0.572242594863986  1572524400  session_0   0.349861113535551   0.788712196665348    0.010245146069704  0.548463092362831  1572524700  session_0   0.150289974958309   0.511662214212664    0.60765291353699   0.178207027680503  1572525000  session_0   0.495015984152799   0.0217641427448981   0.564055630347545  0.652345164013304  1572525300  session_0   0.608194881163176   0.065551216982212    0.938749939090523  0.682270932390147  sqlite> 对于每个指标列，我希望行数大于某个值。在熊猫中，我可以做类似的事情：In [5]: df = df.set_index(['time_secs', 'session'])                                                                                                                                                         但我需要它使用 sqlite。

Answer 1

如果我理解正确，您想要取消透视数据，然后进行一些计数：

SELECT which,

       SUM(CASE WHEN val > 0.5 THEN 1 ELSE 0 END)

FROM (SELECT time_secs, session, 'metric_1' as which, metric_1 as val

      FROM full_ts

      UNION ALL

      SELECT time_secs, session, 'metric_1_likelihood' as which, metric_1_likelihood as val

      FROM full_ts

      SELECT time_secs, session, 'metric_2' as which, metric_2 as val

      FROM full_ts

      UNION ALL

      SELECT time_secs, session, 'metric_2_likelihood' as which, metric_2_likelihood as val

      FROM full_ts

     ) m

GROUP BY which;

编辑：

您可以通过执行以下操作来取消透视：

select c.which,

       (case when c.which = 'metric_1' then t.metric_1

             when c.which = 'metric_1_likelihood' then t.metric_1_likelihood

             . . .

        end) as val

from full_ts t cross join

     (select 'metric_1' as which union all

      select 'metric_1_likelihood' union all

      . . .

    ) c(which)

Answer 2

您需要使用条件聚合，只计算metric大于的行0.5：

SELECT COUNT(CASE WHEN metric_0 > 0.5 THEN 1 END) AS metric_0,

       COUNT(CASE WHEN metric_0_likelihood > 0.5 THEN 1 END) AS metric_0_likelihood,

       COUNT(CASE WHEN metric_1 > 0.5 THEN 1 END) AS metric_1,

       COUNT(CASE WHEN metric_1_likelihood > 0.5 THEN 1 END) AS metric_1_likelihood,

       -- etc etc etc

FROM yourtable

Answer 3

SELECT COUNT(*) FROM full_ts WHERE metric_1 > value