SQL ORACLE -查找连续天数

qpgpyjmq 于 2023-08-03 发布在 Oracle

关注(0)|答案(2)|浏览(124)

我是这个领域的新手，我已经为一个问题挣扎了几天了，这让我发疯了。简而言之：我正在寻找连续几天下订单的用户。举例来说：20230715、20230715、20230716、20230717、20230717、20230718。
不应包括未连续购买的用户，例如：20230715、20230715、20230719。
你能帮我写一个SQL Oracle代码吗？非常感谢您的光临。我也试着为这个主题找到任何解决方案，但到目前为止我还没有找到任何东西。
这是我众多尝试中的一个。除此之外，我还尝试使用了lag（）和lead（）函数，但效果有限。
我想提取连续天数的客户订单。另外，我想在单行/用户中管理匹配的示例，因为稍后，我想计算这些天/用户的支出

WITH ranked_data AS (
    SELECT
        "USER",
        "COMPANY",
        "CREATIONDATE",
        ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
    FROM
        P_TEST_P
)
SELECT
    "USER",
    "COMPANY",
    LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
FROM
    ranked_data
GROUP BY
    "USER",
    "COMPANY";

字符串
输入示例：

USER;Company;creationdate
Liza;Amigurumi KFT;20230712
Liza;Amigurumi KFT;20230713
Liza;Amigurumi KFT;20230714
Liza;Amigurumi KFT;20230715
Liza;Amigurumi KFT;20230728
Liza;Amigurumi KFT;20230718
Liza;Amigurumi KFT;20230722
Liza;Amigurumi KFT;20230723
Liza;Amigurumi KFT;20230724
Liza;Amigurumi KFT;20230729
Liza;Amigurumi KFT;20230729
Liza;Amigurumi KFT;20230729
Liza;Bubu btk;20230703
Liza;Bubu btk;20230703
Liza;Bubu btk;20230708
Liza;Bubu btk;20230711
Liza;Bubu btk;20230722
Liza;PGGNG;20230728
Liza;PGGNG;20230728
Liza;PGGNG;20230728
Liza;PGGNG;20230730
Patrik;YNWA;20230701
Patrik;YNWA;20230706
Patrik;YNWA;20230708
Patrik;YNWA;20230709
Patrik;HUNF;20230725
Patrik;HUNF;20230729
Laszlo;FOUR GRG;20230712
Laszlo;FOUR GRG;20230713
Laszlo;FOUR GRG;20230713
Laszlo;FOUR GRG;20230714

型

oracle

来源：https://stackoverflow.com/questions/76704896/sql-oracle-find-the-consecutive-days

2条答案

按热度按时间

twh00eeo1#

SELECT * FROM (
    SELECT * FROM P_TEST_P 
    MATCH_RECOGNIZE (
        PARTITION BY usr
        ORDER BY creationdate
        MEASURES FIRST(creationdate) AS from_dat, LAST(creationdate) AS to_dat
        PATTERN( conseq+ strt )
        DEFINE 
            conseq AS NEXT(dat) = dat + 1
    )
)
MATCH_RECOGNIZE (
    PARTITION BY usr
    ORDER BY from_dat, to_dat
    MEASURES FIRST(from_dat) AS from_dat, MAX(to_dat) AS to_dat
    PATTERN( merged* strt )
    DEFINE 
        merged AS NEXT(from_dat) = to_dat
)
;

字符串
如果您改变主意并希望考虑公司，只需将其添加到PARTITION BY子句中。

赞(0）回复(0）举报 2023-08-03

gg58donl2#

CREATE TABLE P_TEST_P AS
SELECT 'Liza' AS "USER", 'Amigurumi KFT' AS "COMPANY", TO_DATE('20230712', 'YYYYMMDD') AS "CREATIONDATE" FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230715', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230718', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230723', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230724', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230711', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'Bubu btk', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Liza', 'PGGNG', TO_DATE('20230730', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230701', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230706', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'YNWA', TO_DATE('20230709', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'HUNF', TO_DATE('20230725', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Patrik', 'HUNF', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230712', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL;

31 rows affected

WITH ranked_data AS (
    SELECT
        "USER",
        "COMPANY",
        "CREATIONDATE",
        ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
    FROM
        P_TEST_P
)
SELECT
    "USER",
    "COMPANY",
    LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
FROM
    ranked_data
WHERE
    EXISTS (
        SELECT 1
        FROM ranked_data r2
        WHERE
            r2."USER" = ranked_data."USER"
            AND r2."COMPANY" = ranked_data."COMPANY"
            AND r2.rn = ranked_data.rn + 1
            AND r2."CREATIONDATE" = ranked_data."CREATIONDATE" + 1
    )
GROUP BY
    "USER",
    "COMPANY";

| 公司名称|连续日期| CONSECUTIVE_DATES |
| --|--| ------------ |
| Amigurumi KFT| 2023年7月12日、2023年7月13日、2023年7月14日、2023年7月22日、2023年7月23日、2023年7月28日| 12-JUL-23, 13-JUL-23, 14-JUL-23, 22-JUL-23, 23-JUL-23, 28-JUL-23 |
| 四格|2023年7月12日，2023年7月13日| 12-JUL-23, 13-JUL-23 |
| YNWA| 2023年7月8日| 08-JUL-23 |
fiddle

赞(0）回复(0）举报 2023-08-03

我来回答

SQL ORACLE -查找连续天数

2条答案

相关问题

热门标签

最新问答