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     ) mGROUP 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 valfrom 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 etcFROM yourtable

Answer 3

SELECT COUNT(*) FROM full_ts WHERE metric_1 > value