代码之家 › 专栏 › 技术社区 › user3871

连续整数的和和和和组

gaps-and-islands group-by postgresql sql

user3871 · 技术社区 · 6 年前

我需要对连续整数求和并用 spoken_correctly gt;0。

我可以通过观察找出哪些部分是连续的 lag 和 lead ,但不确定如何对连续组求和 consecutive 字段的值。

也就是说,我有两组连续的 说话正确 值>0。绿色的第一组有三行非零 说话正确 绿色的第二组有两个。

期望输出:

此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

1 回复 | 直到 6 年前

Kaushik Nayak 6 年前

这是一个缺口和孤岛问题,可以通过使用 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;

Demo

推荐文章

John Smith · Java按值对映射进行分组,其中值为列表

2 年前

The Great · 拆分并存储数据帧,但名称基于特定列中的唯一值

2 年前

Programming Noob · AttributeError:“SeriesGroupBy”对象没有属性“tolist”

2 年前

Flo · 分组依据中的SQL大小写

2 年前

user13663655 · 我可以使用条件分组并返回源类型值吗

3 年前

pinkiBet · 使用MAX和UNION分组,还是加入?

3 年前

Shubham · 如何在R中查找数据表的两个多类别列之间的差异/setdiff()

3 年前

user7052482 · 将来自联合的SQL结果分组(SQL Server)

7 年前

lugger1 · 如何获取Postgres中时间间隔的平均值

7 年前

JustCallMeGary · 如何使用R中的一个因子操作两个变量[重复]

7 年前