SQL ORACLE – 查找连续的日期

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

SQL ORACLE - Find the consecutive days

问题

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

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

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

以下是我尝试之一。除此之外，我还尝试使用lag()和lead()函数，但成功有限。

我想提取具有连续日期的客户订单。此外，我希望将匹配的实例管理在单一行/用户中，因为稍后我想计算这些日期/用户的支出。

  1. WITH ranked_data AS (
  2.     SELECT
  3.         "USER",
  4.         "COMPANY",
  5.         "CREATIONDATE",
  6.         ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
  7.     FROM
  8.         P_TEST_P
  9. )
  10. SELECT
  11.     "USER",
  12.     "COMPANY",
  13.     LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
  14. FROM
  15.     ranked_data
  16. GROUP BY
  17.     "USER",
  18.     "COMPANY";

输入示例：

  1. USER;Company;creationdate
  2. Liza;Amigurumi KFT;20230712
  3. Liza;Amigurumi KFT;20230713
  4. Liza;Amigurumi KFT;20230714
  5. Liza;Amigurumi KFT;20230715
  6. Liza;Amigurumi KFT;20230728
  7. Liza;Amigurumi KFT;20230718
  8. Liza;Amigurumi KFT;20230722
  9. Liza;Amigurumi KFT;20230723
  10. Liza;Amigurumi KFT;20230724
  11. Liza;Amigurumi KFT;20230729
  12. Liza;Amigurumi KFT;20230729
  13. Liza;Amigurumi KFT;20230729
  14. Liza;Bubu btk;20230703
  15. Liza;Bubu btk;20230703
  16. Liza;Bubu btk;20230708
  17. Liza;Bubu btk;20230711
  18. Liza;Bubu btk;20230722
  19. Liza;PGGNG;20230728
  20. Liza;PGGNG;20230728
  21. Liza;PGGNG;20230728
  22. Liza;PGGNG;20230730
  23. Patrik;YNWA;20230701
  24. Patrik;YNWA;20230706
  25. Patrik;YNWA;20230708
  26. Patrik;YNWA;20230709
  27. Patrik;HUNF;20230725
  28. Patrik;HUNF;20230729
  29. Laszlo;FOUR GRG;20230712
  30. Laszlo;FOUR GRG;20230713
  31. Laszlo;FOUR GRG;20230713
  32. 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

  2. WITH ranked_data AS (
  3.     SELECT
  4.         "USER",
  5.         "COMPANY",
  6.         "CREATIONDATE",
  7.         ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
  8.     FROM
  9.         P_TEST_P
  10. )
  11. SELECT
  12.     "USER",
  13.     "COMPANY",
  14.     LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
  15. FROM
  16.     ranked_data
  17. GROUP BY
  18.     "USER",
  19.     "COMPANY";

Input example:

  1. USER;Company;creationdate
  2. Liza;Amigurumi KFT;20230712
  3. Liza;Amigurumi KFT;20230713
  4. Liza;Amigurumi KFT;20230714
  5. Liza;Amigurumi KFT;20230715
  6. Liza;Amigurumi KFT;20230728
  7. Liza;Amigurumi KFT;20230718
  8. Liza;Amigurumi KFT;20230722
  9. Liza;Amigurumi KFT;20230723
  10. Liza;Amigurumi KFT;20230724
  11. Liza;Amigurumi KFT;20230729
  12. Liza;Amigurumi KFT;20230729
  13. Liza;Amigurumi KFT;20230729
  14. Liza;Bubu btk;20230703
  15. Liza;Bubu btk;20230703
  16. Liza;Bubu btk;20230708
  17. Liza;Bubu btk;20230711
  18. Liza;Bubu btk;20230722
  19. Liza;PGGNG;20230728
  20. Liza;PGGNG;20230728
  21. Liza;PGGNG;20230728
  22. Liza;PGGNG;20230730
  23. Patrik;YNWA;20230701
  24. Patrik;YNWA;20230706
  25. Patrik;YNWA;20230708
  26. Patrik;YNWA;20230709
  27. Patrik;HUNF;20230725
  28. Patrik;HUNF;20230729
  29. Laszlo;FOUR GRG;20230712
  30. Laszlo;FOUR GRG;20230713
  31. Laszlo;FOUR GRG;20230713
  32. Laszlo;FOUR GRG;20230714

答案1

得分: 2

  1. SELECT * FROM (
  2. 	SELECT * FROM P_TEST_P 
  3. 	MATCH_RECOGNIZE (
  4. 		PARTITION BY usr
  5. 		ORDER BY creationdate
  6. 		MEASURES FIRST(creationdate) AS from_dat, LAST(creationdate) AS to_dat
  7. 		PATTERN( conseq+ strt )
  8. 		DEFINE 
  9. 			conseq AS NEXT(dat) = dat + 1
  10. 	)
  11. )
  12. MATCH_RECOGNIZE (
  13. 	PARTITION BY usr
  14. 	ORDER BY from_dat, to_dat
  15. 	MEASURES FIRST(from_dat) AS from_dat, MAX(to_dat) AS to_dat
  16. 	PATTERN( merged* strt )
  17. 	DEFINE 
  18. 		merged AS NEXT(from_dat) = to_dat
  19. )
  20. ;
  22. If you change your mind and want to take into account the company, just add it in the PARTITION BY clauses.
英文: 
  1. SELECT * FROM (
  2. 	SELECT * FROM P_TEST_P 
  3. 	MATCH_RECOGNIZE (
  4. 		PARTITION BY usr
  5. 		ORDER BY creationdate
  6. 		MEASURES FIRST(creationdate) AS from_dat, LAST(creationdate) AS to_dat
  7. 		PATTERN( conseq+ strt )
  8. 		DEFINE 
  9. 			conseq AS NEXT(dat) = dat + 1
  10. 	)
  11. )
  12. MATCH_RECOGNIZE (
  13. 	PARTITION BY usr
  14. 	ORDER BY from_dat, to_dat
  15. 	MEASURES FIRST(from_dat) AS from_dat, MAX(to_dat) AS to_dat
  16. 	PATTERN( merged* strt )
  17. 	DEFINE 
  18. 		merged AS NEXT(from_dat) = to_dat
  19. )
  20. ;

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

答案2

得分: 2

  1. CREATE TABLE P_TEST_P AS
  2. SELECT 'Liza' AS "USER", 'Amigurumi KFT' AS "COMPANY", TO_DATE('20230712', 'YYYYMMDD') AS "CREATIONDATE" FROM DUAL UNION ALL
  3. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
  4. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL UNION ALL
  5. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230715', 'YYYYMMDD') FROM DUAL UNION ALL
  6. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  7. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230718', 'YYYYMMDD') FROM DUAL UNION ALL
  8. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
  9. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230723', 'YYYYMMDD') FROM DUAL UNION ALL
  10. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230724', 'YYYYMMDD') FROM DUAL UNION ALL
  11. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  12. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  13. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  14. SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
  15. SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
  16. SELECT 'Liza', 'Bubu btk', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
  17. SELECT 'Liza', 'Bubu btk', TO_DATE('20230711', 'YYYYMMDD') FROM DUAL UNION ALL
  18. SELECT 'Liza', 'Bubu btk', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
  19. SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  20. SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  21. SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  22. SELECT 'Liza', 'PGGNG', TO_DATE('20230730', 'YYYYMMDD') FROM DUAL UNION ALL
  23. SELECT 'Patrik', 'YNWA', TO_DATE('20230701', 'YYYYMMDD') FROM DUAL UNION ALL
  24. SELECT 'Patrik', 'YNWA', TO_DATE('20230706', 'YYYYMMDD') FROM DUAL UNION ALL
  25. SELECT 'Patrik', 'YNWA', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
  26. SELECT 'Patrik', 'YNWA', TO_DATE('20230709', 'YYYYMMDD') FROM DUAL UNION ALL
  27. SELECT 'Patrik', 'HUNF', TO_DATE('20230725', 'YYYYMMDD') FROM DUAL UNION ALL
  28. SELECT 'Patrik', 'HUNF', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  29. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230712', 'YYYYMMDD') FROM DUAL UNION ALL
  30. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
  31. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
  32. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL;
  34. WITH ranked_data AS (
  35.     SELECT
  36.         "USER",
  37.         "COMPANY",
  38.         "CREATIONDATE",
  39.         ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
  40.     FROM
  41.         P_TEST_P
  42. )
  43. SELECT
  44.     "USER",
  45.     "COMPANY",
  46.     LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
  47. FROM
  48.     ranked_data
  49. WHERE
  50.     EXISTS (
  51.         SELECT 1
  52.         FROM ranked_data r2
  53.         WHERE
  54.             r2."USER" = ranked_data."USER"
  55.             AND r2."COMPANY" = ranked_data."COMPANY"
  56.             AND r2.rn = ranked_data.rn + 1
  57.             AND r2."CREATIONDATE" = ranked_data."CREATIONDATE" + 1
  58.     )
  59. GROUP BY
  60.     "USER",
  61.     "COMPANY";
英文: 
  1. CREATE TABLE P_TEST_P AS
  2. SELECT 'Liza' AS "USER", 'Amigurumi KFT' AS "COMPANY", TO_DATE('20230712', 'YYYYMMDD') AS "CREATIONDATE" FROM DUAL UNION ALL
  3. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
  4. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL UNION ALL
  5. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230715', 'YYYYMMDD') FROM DUAL UNION ALL
  6. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  7. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230718', 'YYYYMMDD') FROM DUAL UNION ALL
  8. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
  9. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230723', 'YYYYMMDD') FROM DUAL UNION ALL
  10. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230724', 'YYYYMMDD') FROM DUAL UNION ALL
  11. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  12. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  13. SELECT 'Liza', 'Amigurumi KFT', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  14. SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
  15. SELECT 'Liza', 'Bubu btk', TO_DATE('20230703', 'YYYYMMDD') FROM DUAL UNION ALL
  16. SELECT 'Liza', 'Bubu btk', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
  17. SELECT 'Liza', 'Bubu btk', TO_DATE('20230711', 'YYYYMMDD') FROM DUAL UNION ALL
  18. SELECT 'Liza', 'Bubu btk', TO_DATE('20230722', 'YYYYMMDD') FROM DUAL UNION ALL
  19. SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  20. SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  21. SELECT 'Liza', 'PGGNG', TO_DATE('20230728', 'YYYYMMDD') FROM DUAL UNION ALL
  22. SELECT 'Liza', 'PGGNG', TO_DATE('20230730', 'YYYYMMDD') FROM DUAL UNION ALL
  23. SELECT 'Patrik', 'YNWA', TO_DATE('20230701', 'YYYYMMDD') FROM DUAL UNION ALL
  24. SELECT 'Patrik', 'YNWA', TO_DATE('20230706', 'YYYYMMDD') FROM DUAL UNION ALL
  25. SELECT 'Patrik', 'YNWA', TO_DATE('20230708', 'YYYYMMDD') FROM DUAL UNION ALL
  26. SELECT 'Patrik', 'YNWA', TO_DATE('20230709', 'YYYYMMDD') FROM DUAL UNION ALL
  27. SELECT 'Patrik', 'HUNF', TO_DATE('20230725', 'YYYYMMDD') FROM DUAL UNION ALL
  28. SELECT 'Patrik', 'HUNF', TO_DATE('20230729', 'YYYYMMDD') FROM DUAL UNION ALL
  29. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230712', 'YYYYMMDD') FROM DUAL UNION ALL
  30. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
  31. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230713', 'YYYYMMDD') FROM DUAL UNION ALL
  32. SELECT 'Laszlo', 'FOUR GRG', TO_DATE('20230714', 'YYYYMMDD') FROM DUAL;
  1. 31 rows affected
  1. WITH ranked_data AS (
  2. SELECT
  3. "USER",
  4. "COMPANY",
  5. "CREATIONDATE",
  6. ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
  7. FROM
  8. P_TEST_P
  9. )
  10. SELECT
  11. "USER",
  12. "COMPANY",
  13. LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
  14. FROM
  15. ranked_data
  16. WHERE
  17. EXISTS (
  18. SELECT 1
  19. FROM ranked_data r2
  20. WHERE
  21. r2."USER" = ranked_data."USER"
  22. AND r2."COMPANY" = ranked_data."COMPANY"
  23. AND r2.rn = ranked_data.rn + 1
  24. AND r2."CREATIONDATE" = ranked_data."CREATIONDATE" + 1
  25. )
  26. GROUP BY
  27. "USER",
  28. "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
  • oracle
  • sql
匿名

发表评论

匿名网友

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

确定