如何通过连续日期变量检查值

Question

我在雪花中有数据库表，如果有flag_1 == 1在至少3天时，我需要检查每个客户。 FLAG_1指示该订单是否包含任何特定商品。并使用customer_id和flag_2创建新表。我真的不知道如何处理这个问题。

示例表：

CREATE TABLE TMP_TEST
(
CUSTOMER_ID INT,
ORDER_DATE DATE,
FLAG_1 INT
);


INSERT INTO TMP_TEST (CUSTOMER_ID, ORDER_DATE, FLAG_1)
VALUES
  (001, '2020-04-01', 0),
  (001, '2020-04-02', 1),
  (001, '2020-04-03', 1),
  (001, '2020-04-04', 1),
  (001, '2020-04-05', 1),
  (001, '2020-04-06', 0),
  (001, '2020-04-07', 0),
  (001, '2020-04-08', 0),
  (001, '2020-04-09', 1),
  (002, '2020-04-10', 1),
  (002, '2020-04-11', 0),
  (002, '2020-04-12', 0),
  (002, '2020-04-13', 1),
  (002, '2020-04-14', 1),
  (002, '2020-04-15', 0),
  (002, '2020-04-16', 1),
  (002, '2020-04-17', 1);

预期输出表：

CUSTOMER_ID   FLAG_2
001   1
002   0

Answer 1

也许这会有所帮助：

with calcflag as (
select customer_id, IFF( sum(flag_1) over (PARTITION by customer_id order by order_date rows between 3 preceding and 1 preceding) = 3, 1, 0 ) as new_flag 
from tmp_Test)
select customer_id, max(new_flag) flag_2
from calcflag
group by 1
order by 1;

+-------------+--------+
| CUSTOMER_ID | FLAG_2 |
+-------------+--------+
|           1 |      1 |
|           2 |      0 |
+-------------+--------+

Answer 2

使用count_if也有效：

with calcflag as (
    select 
        customer_id, 
        IFF(
            count_if(flag_1 = 1) over (
                PARTITION by customer_id 
                order by order_date 
                rows between 2 preceding and current row
            ) = 3, 1, 0
        ) as new_flag 
    from tmp_Test
)
select 
    customer_id, 
    max(new_flag) flag_2
from calcflag
group by 1

+-------------+--------+
| CUSTOMER_ID | FLAG_2 |
|-------------+--------|
|           1 |      1 |
|           2 |      0 |
+-------------+--------+

Answer 3

雪花支持

要查找3个或更多事件，模式为模式（a {3，}）：

SELECT *
FROM TMP_TEST
MATCH_RECOGNIZE (
    PARTITION BY CUSTOMER_ID
    ORDER BY ORDER_DATE
    MEASURES MATCH_NUMBER() AS mn
    ALL ROWS PER MATCH WITH UNMATCHED ROWS
    PATTERN ( a{3,} )
    DEFINE a AS FLAG_1 = 1
) mr
ORDER BY CUSTOMER_ID, ORDER_DATE;

output：

---

每组单行倒数

SELECT CUSTOMER_ID, COALESCE(MIN(MN),0) AS FLAG_2
FROM TMP_TEST
MATCH_RECOGNIZE (
    PARTITION BY CUSTOMER_ID
    ORDER BY ORDER_DATE
    MEASURES MATCH_NUMBER() AS mn
    ALL ROWS PER MATCH WITH UNMATCHED ROWS
    PATTERN ( a{3,})
    DEFINE a AS FLAG_1 = 1
) mr
GROUP BY CUSTOMER_ID;

输出：

---

此解决方案的功能位于模式部分，可以在新条件下轻松扩展。例如：

PATTERN ( a b{1,2} a )
DEFINE a AS FLAG_1 = 1,
       b AS FLAT_2 = 0;

此处：查找flag = 1的序列，然后是一两个出现flag = 0的事件，并以flag = 1结束。