sql - 计算连续的 TRUE 值并在单独的列中显示为 TRUE 值的计数

Question

雪花表中的样本数据：

| EMAIL | Date |  EmailClicked|
| -------- | -------------- | -------- |
| [e-mail]| 01-01-2021| TRUE |
| [e-mail]| 02-01-2021| FALSE|
| [e-mail]| 02-01-2021| FALSE|
| [e-mail]| 03-01-2021| TRUE |
| [e-mail]| 04-01-2021| TRUE |
| [e-mail]| 05-01-2021| TRUE |
| [e-mail]| 03-01-2021| TRUE|
| [e-mail]| 06-01-2021| FALSE|
| [e-mail]| 06-01-2021| FALSE|
| [e-mail]| 07-01-2021| TRUE |
| [e-mail]| 08-01-2021| TRUE |

输出预期将连续的真值显示为另一列

| EMAIL | Date |  EmailClicked| Consecutivecount |
| -------- | -------------- | -------- | -------- |
| [e-mail]| 01-01-2021| TRUE | 1 |
| [e-mail]| 02-01-2021| FALSE| 0 |
| [e-mail]| 03-01-2021| TRUE | 1 |
| [e-mail]| 04-01-2021| TRUE | 2 |
| [e-mail]| 05-01-2021| TRUE | 3 |
| [e-mail]| 06-01-2021| FALSE| 0 |
| [e-mail]| 07-01-2021| TRUE | 1 |
| [e-mail]| 08-01-2021| TRUE | 2 |
| [e-mail]| 02-01-2021| FALSE| 0 |
| [e-mail]| 03-01-2021| TRUE| 1 |
| [e-mail]| 06-01-2021| FALSE| 0 |

标签： sqlsnowflake-cloud-data-platform

Answer 1

这是一个间隙和孤岛问题，一种方法使用行数差异方法：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Date) rn1,
              ROW_NUMBER() OVER (PARTITION BY EMAIL, EmailClicked ORDER BY Date) rn2
    FROM yourTable
)

SELECT EMAIL, Date, EmailClicked,
       EmailClicked::int * ROW_NUMBER() OVER (PARTITION BY EMAIL, EmailClicked, rn1-rn2
                                    ORDER BY Date) AS Consecutivecount
FROM cte
ORDER BY EMAIL, Date;

演示