sql - 对连续整数求和和分组
问题描述
我需要对连续整数进行求和和分组spoken_correctly
> 0。
我可以通过查看lag
and找出哪些部分是连续的lead
,但是我不确定如何对连续组consecutive
字段的值求和。
即,我有两组连续spoken_correctly
值> 0。绿色的第一组有三行非零spoken_correctly
,绿色的第二组有两行。
期望的输出:
此 SQL 生成输出上方的第一张图像:
select *, case when (q.times_spoken_correctly > 0 and (q.lag > 0 or q.lead > 0)) then 1 else 0 end as consecutive
from (
select *, lag(q.times_spoken_correctly) over (partition by q.profile_id order by q.profile_id) as lag, lead(q.times_spoken_correctly) over (partition by q.profile_id order by q.profile_id) as lead
from (
SELECT *
FROM ( VALUES (3, 0, '2019-01-15 19:15:06'),
(3, 0, '2019-01-15 19:15:07'),
(3, 1, '2019-01-15 19:16:06'),
(3, 2, '2019-01-15 19:16:10'),
(3, 2, '2019-01-15 19:17:06'),
(3, 0, '2019-01-15 19:17:11'),
(3, 0, '2019-01-15 19:39:06'),
(3, 3, '2019-01-15 19:40:10'),
(3, 4, '2019-01-15 19:40:45')
) AS baz ("profile_id", "times_spoken_correctly", "w_created_at")
) as q
) as q
解决方案
这是一个间隙和孤岛问题,可以通过使用以下方法形成序列组来解决row_number
select profile_id, count(*) as consec FROM
(
SELECT t.*, row_number() OVER ( PARTITION BY profile_id ORDER BY w_created_at ) -
row_number() OVER ( PARTITION BY profile_id, CASE times_spoken_correctly
WHEN 0 THEN 0 ELSE 1 END
ORDER BY w_created_at ) as seq --group zeros and non zeros
FROM t ORDER BY w_created_at
) s WHERE times_spoken_correctly > 0 --to count only "> zero" groups.
GROUP BY profile_id,seq;
推荐阅读
- unity3d - 删除项目目录时不加载主页和新项目页面
- angular - typeerror:将圆形结构转换为json angular 6
- python - Python:如何解析下一行?
- sql - 想要在 Postgresql 中不同表的多行中分配表中的金额
- javascript - 在画布中旋转后的坐标问题
- unit-testing - 如何只配置一次 vertx 实例本身并在应用程序代码和测试中使用它?
- c# - IErrorDataInfo 验证框不可见
- java - ANDROID - 如何从单选按钮获取文本并将其插入 sqlite 数据库?
- asp.net-core - ASP .Net Core 上的 RSA 和 SignedCms;需要多平台方法
- git - 检测/防止对 git 中的文件进行不必要的更改