连续项检查总数的sql查询

rsl1atfo 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(334)

sqlite/sql查询连续记录的总数，pfa图像。这是表中的1和0。假设我想检查1的总数，它正好是3的倍数，或者你可以说是countius。如果出现零，那就不考虑了。
示例a:—（列值水平显示）

1 1 1 1 0 0 0 0 0 1 1 1 0 0 0 1 1 1 0 1 1 0 1 0 1 1 1 1 1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 1 1 1 0 1 1

所以发生的总次数是8除以3。

[编辑2]将第一列视为百万美元，并按赞成的顺序排列

sql sqlite android-room android-sqlite

来源：https://stackoverflow.com/questions/63047132/sql-query-for-total-number-of-continuous-item-check

1条答案

按热度按时间

zte4gxcn1#

考虑到我们可以使用 deviceid 对行进行排序并计算序列
注意，为了工作，您至少应该有sqlite版本3.25，因为这需要窗口函数
创建具有自动增量的测试表 deviceid 列，它将模拟您自己的数据

CREATE TABLE "test" (
    "deviceid"  INTEGER,
    "password"  INTEGER,
    PRIMARY KEY("deviceid" AUTOINCREMENT)
);

按自己的顺序插入行 1 1 1 1 0 0 0 0 0 1 1 1 0 0 0 1 1 1 0 1 1 0 1 0 1 1 1 1 1 1 1 1 0 1 1 0 1 1 1 1 1 1 1 1 1 1 0 1 1 ```
DELETE FROM test;
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (0);
INSERT INTO test (password) VALUES (1);
INSERT INTO test (password) VALUES (1);

解决方案：

select password, sum(div_floor) AS Number_of_3_occurences
FROM (
select password, count() as cnt_sequence, cast(round(count() / 3 ) as int) as div_floor
from (select test.*,
(row_number() over (order by deviceid) -
row_number() over (partition by password order by deviceid)
) as grp
from test
) t
group by grp, password
) ttt
where cnt_sequence>=3
group by password

结果

password Number_of_3_occurences
0 2
1 8

赞(0）回复(0）举报 2021-07-24

我来回答

连续项检查总数的sql查询

1条答案

相关问题

热门标签

最新问答