sqlite中实现字符串切割成列之后，转json求最大值

败走华容道

已于 2023-05-04 12:46:44 修改

阅读量479

点赞数

文章标签： sqlite json 数据库

于 2023-04-28 17:08:25 首次发布

本文链接：https://blog.csdn.net/qq_38419885/article/details/130430442

版权

该SQL查询从abnormal_uidelay_data表中获取所有前台处理延迟不为0且屏幕状态为1的记录，然后通过JSON_EXTRACT解析uidelay_list字段找到最大延迟时间并筛选出大于3000ms的条目。通过LEFTJOIN确保所有原始记录都被考虑，即使没有匹配的延迟时间。

摘要由CSDN通过智能技术生成

表结构

实现的sql

SELECT COUNT(t3.id)
FROM abnormal_uidelay_data t3
LEFT JOIN (
 SELECT id, max(JSON_EXTRACT(splid, '$.delayTimeMs')) as max_delayTimeMs
 FROM (
  with split(id,splid,idsstr) as (
   select  id, '', uidelay_list||',{' from abnormal_uidelay_data   
   UNION ALL 
   SELECT id,substr(idsstr, 0, instr(idsstr, '},{')+ 1), substr(idsstr, instr(idsstr, '},{') + 2) FROM split WHERE idsstr != '' )
 select id, splid from split where splid != '' order by id 
 ) t1
 GROUP BY id ) t2 on t2.id = t3.id 
WHERE t3.if_foreground_process_delay != 0 and screen_status = 1 and t2.max_delayTimeMs > 3000

创建表的sql

CREATE TABLE "abnormal_uidelay_data" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "uidelay_list" TEXT,
  "if_foreground_process_delay" TEXT,
  "screen_status" TEXT
);

插入示例数据的sql

INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (1, '{"a":1,"b":0,"delayTimeMs":10,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":3000,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (2, '{"a":1,"b":0,"delayTimeMs":10,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (3, '{"a":1,"b":0,"delayTimeMs":40,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":3001,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (4, '{"a":1,"b":0,"delayTimeMs":50,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (5, '{"a":1,"b":0,"delayTimeMs":40,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":30,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (6, '{"a":1,"b":0,"delayTimeMs":50,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (7, '{"a":1,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":30,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (8, '{"a":1,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (9, '{"a":1,"b":0,"delayTimeMs":40,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":30,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (10, '{"a":1,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (11, '{"a":1,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":3002,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (12, '{"a":1,"b":0,"delayTimeMs":90,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '1', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (38, '{"a":1,"b":0,"delayTimeMs":50,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '0', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (39, '{"a":1,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":10,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":30,"d":0,"e":0}', '0', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (40, '{"a":1,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '0', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (41, '{"a":1,"b":0,"delayTimeMs":40,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":30,"d":0,"e":0}', '0', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (42, '{"a":1,"b":0,"delayTimeMs":70,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":20,"d":0,"e":0}', '0', '1');
INSERT INTO "main"."abnormal_uidelay_data" ("id", "uidelay_list", "if_foreground_process_delay", "screen_status") VALUES (43, '{"a":1,"b":0,"delayTimeMs":20,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":10,"d":0,"e":0},{"a":0,"b":0,"delayTimeMs":30,"d":0,"e":0}', '0', '1');

运行结果：
在这里插入图片描述

如果没有主键，可以使用多字段拼接

SELECT COUNT(*)
FROM abnormal_uidelay_data t3
LEFT JOIN (
 SELECT id, max(JSON_EXTRACT(splid, '$.delayTimeMs')) as max_delayTimeMs
 FROM (
  with split(id,splid,idsstr) as (
   select timestamp||'-'||uidelay_list||'-'||if_foreground_process_delay||'-'||screen_status, '', uidelay_list||',{' from abnormal_uidelay_data   
   UNION ALL 
   SELECT id,substr(idsstr, 0, instr(idsstr, '},{')+ 1), substr(idsstr, instr(idsstr, '},{') + 2) FROM split WHERE idsstr != '' )
 select id, splid from split where splid != '' order by id 
 ) t1
 GROUP BY id ) t2 on t2.id = t3.timestamp||'-'||uidelay_list||'-'||if_foreground_process_delay||'-'||screen_status
WHERE t3.if_foreground_process_delay != 0 and screen_status = 1 and t2.max_delayTimeMs > 3000