sqlite 数据库：获取按 sum(col2)

我相信以下查询可以在一次执行中完成您想要的所有操作：-

WITH  
    intermediate AS (SELECT 
    timestamp,CASE WHEN bytes <48 THEN 1 ELSE 0 END AS r1,CASE WHEN bytes >= 48 AND bytes < 96 THEN 1 ELSE 0 END AS r2,CASE WHEN bytes >= 96 AND bytes < 144 THEN 1 ELSE 0 END AS r3,CASE WHEN bytes >= 144 AND bytes < 192 THEN 1 ELSE 0 END AS r4,CASE WHEN bytes >= 192 THEN 1 ELSE 0 END AS r5
    FROM events  /* >>>>> INCLUDE YOUR WHERE CLAUSE HERE <<<<< */)
SELECT 
    strftime('%Y-%m-%d %H:00:00',timestamp) AS 'date time',sum(r1) AS '0Bytes',sum(r2) AS '48Bytes',sum(r3) AS '96Bytes',sum(r4) AS '144Bytes',sum(r5) AS '192Bytes'
FROM intermediate
GROUP BY strftime('%Y-%m-%d %H',timestamp)
;

WHERE 子句尚未包含在内，但已指明它应放在何处。 您只需修改查询以包含 WHERE 子句，然后替换现有查询。 所有的工作都由查询完成。您只需提取报告的行。

说明

从事件表创建一个中间 CTE（通用表表达式（基本上是一个临时表））。此表包含列：-

• 从事件表中完全复制的时间戳
• r1-r5 列使用 CASE WHEN THEN ELSE END 构造根据 WHEN 条件将 0 或 1 放入列中

然后将 CTE 用作最终 SELECT QUERY 的输入（您可以有多个 CTE 和）。

这个最终查询根据 YYYY-MM-DD HH 部分的 时间戳，对每个 r1-r5 求和，以便计算相应字节范围内的命中数。更改列名以反映要打印/输出/显示的列名。

YYYY-MM-DD HH 后跟 :00:00 作为 date_time 列输出（再次根据报告）。

WITH 开头基本上是说 WITH 以下 CTE 做某事（不一定是 SELECT，可以是 updatE、deletE 或 INSERT）见 The WITH clause

示例

drop table IF EXISTS events;
create table IF NOT EXISTS events (timestamp TEXT PRIMary KEY,sensorid TEXT,operation TEXT,bytes IntegeR);
INSERT INTO events VALUES 
    ('2021-04-01 01:12:44','ID1','RX_DATA',48),('2021-04-01 01:12:54','ID2',96),('2021-04-01 01:13:10','ID3',0),('2021-04-01 01:12:15','ID4',144),('2021-04-01 01:14:05',192),('2021-04-01 01:13:25','ID5',('2021-04-01 01:13:54',('2021-04-01 01:14:54',('2021-04-01 02:12:54',('2021-04-01 03:12:54',('2021-04-01 04:12:54',96)
;

WITH  
    intermediate AS (SELECT 
    timestamp,CASE WHEN bytes >= 192 THEN 1 ELSE 0 END AS r5
    FROM events  /* >>>>> INCLUDE YOUR WHERE CLAUSE HERE <<<<< */ )
SELECT 
    strftime('%Y-%m-%d %H:00:00',timestamp)
;
drop table IF EXISTS events;

结果

每行生成总数会很棒。

以下将包括总数（请参阅对上述更改的评论）

WITH  
    intermediate AS (SELECT 
    timestamp,CASE WHEN bytes >= 192 THEN 1 ELSE 0 END AS r5,bytes /*<<<<<<<<<< ADDED */
    FROM events  /* >>>>> INCLUDE YOUR WHERE CLAUSE HERE <<<<< */)
SELECT 
    strftime('%Y-%m-%d %H:00:00',sum(r5) AS '192Bytes',sum(bytes) AS @R_482_10586@l /*<<<<<<<<<< ADDED */
FROM intermediate
GROUP BY strftime('%Y-%m-%d %H',timestamp)
;

结果将是：-

另外，我想让它更通用，数据大小会自动计算出来。

这根本不是特定的。但是，您可以轻松地以编程方式构建 SQL CASE 子句和关联的 sum 子句，然后构建整个查询以供执行。

如果我理解正确，结果集中的每一行代表每组中有多少传感器。如果是这样，您需要两个级别的聚合，一个在设备/小时级别，另一个在小时级别。

内部聚合汇总了一天中传感器的总字节数。然后外部只是旋转它以获得每组中的计数：

SELECT yyyymmddh,SUM(bytes_in_day = 0) as byes_0,SUM(bytes_in_day = 48) as byes_48,SUM(bytes_in_day = 96) as byes_96,SUM(bytes_in_day = 144) As byes_144,SUM(bytes_in_day = 192) as byes_192,SUM(bytes_in_day NOT IN (0,48,96,144,192)) as bytes_other
FROM (SELECT strftime('%Y-%m-%d %H:00:00',timestamp) as yyyymmddhh,sensor,SUM(bytes) as bytes_in_hour,SUM(SUM(bytes)) OVER (PARTITION BY date(timestamp)) as bytes_in_day
      FROM events
      GROUP BY yyyymmddhh,sensor
     ) hs
GROUP BY yyyymmddhh;

如果要按天过滤，可以在子查询中添加WHERE子句。