SQL ORACLE – 查找连续的日期

huangapple go评论69阅读模式
英文:

SQL ORACLE - Find the consecutive days

问题

我是这个领域的新手,我已经为这个问题苦苦挣扎了几天,它让我发疯了。简而言之,我正在寻找连续几天下订单的用户。例如:20230715、20230715、20230716、20230717、20230717、20230718。

不应包括未连续购买的用户,例如:20230715、20230715、20230719。

你能帮我编写一个适用于Oracle SQL的代码吗?非常感谢。我也尝试找到这个主题的任何解决方案,但迄今为止还没有找到。

以下是我尝试之一。除此之外,我还尝试使用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
英文:

I am newbie on the field and I've been struggling with a problem for a few days now, and it's driving me crazy. In short: I'm looking for users who placed orders on consecutive days. For example: 20230715, 20230715, 20230716, 20230717, 20230717, 20230718.

Users who did not make purchases consecutively should not be included, for example: 20230715, 20230715, 20230719.

Could you help me write an SQL Oracle code for this? Thank you very much. I also tried to find any solution for this topic, but I haven't found anything so far.

Here is one of my attempts among many. In addition to this, I also tried using the lag() and lead() functions, but with limited success.

I want to extract the orders of customers where consecutive days are found. Also i wanted to manage matching instances in a single row/users because later, i would like to calculate the spendings at those days/users


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";

Input example:

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

答案1

得分: 2

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
)
;

If you change your mind and want to take into account the company, just add it in the PARTITION BY clauses.
英文:
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
)
;

If you change your mind and want to take into account the company, just add it in the PARTITION BY clauses.

答案2

得分: 2

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;

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";
英文:
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";
USER COMPANY CONSECUTIVE_DATES
Liza Amigurumi KFT 12-JUL-23, 13-JUL-23, 14-JUL-23, 22-JUL-23, 23-JUL-23, 28-JUL-23
Laszlo FOUR GRG 12-JUL-23, 13-JUL-23
Patrik YNWA 08-JUL-23

fiddle

huangapple
  • 本文由 发表于 2023年7月17日 21:21:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/76704896.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定