当sqlite中完全显示出野外值时，我将如何确定野外值出现的速率？

Question

我有一个巨大的数据集。每个记录上都有一个时间戳（以字符串格式）。我正在尝试确定特定字段是一个特定值的次数。以一种更具体的方式：

+---------------------+-------+
| TimeStamp           | Value |
+---------------------+-------+
| 2022-01-01 00:00:00 | A     | A shows up 3 times in the first hour
| 2022-01-01 00:00:01 | B     |
| 2022-01-01 00:00:05 | A     |
| 2022-01-01 00:00:09 | A     |
| 2022-01-01 05:00:01 | A     | A shows up 2 times in the sixth hour
| 2022-01-01 05:00:02 | A     |
+---------------------+-------+

我想看看一个小时内a平均每小时出现多少次，不包括根本没有出现的时间。在这种情况下，5/2 =＆gt;每小时2.5次。 SQL是否可以做到这一点，还是我必须编写一个外部脚本来执行此操作？

Answer 1

您需要2个聚合级别：

SELECT AVG(counter) average
FROM (
  SELECT COUNT(*) counter
  FROM tablename
  WHERE Value = 'A'
  GROUP BY strftime('%Y-%m-%d %H', TimeStamp)
);

或，使用avg（）窗口函数：

SELECT DISTINCT AVG(COUNT(*)) OVER () average
FROM tablename
WHERE Value = 'A'
GROUP BY strftime('%Y-%m-%d %H', TimeStamp);

如果您想要所有value s：

SELECT Value, AVG(counter) average
FROM (
  SELECT Value, COUNT(*) counter
  FROM tablename
  GROUP BY Value, strftime('%Y-%m-%d %H', TimeStamp)
)
GROUP BY Value;

或：

SELECT DISTINCT Value, 
       AVG(COUNT(*)) OVER (PARTITION BY Value) average
FROM tablename
GROUP BY Value, strftime('%Y-%m-%d %H', TimeStamp);

请参阅 demo 。
。