SQL用于获取两个日期范围之间的计数。

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

SQL to fetch the count between tow date range

问题

我需要获取在00:15:00和01:15:00之间以及随后的任何日期范围内的PRODUCT_ID计数。

示例脚本：-

我的数据库结构和数据如下。

  1. CREATE TABLE time1 (cr_date date , product_id number );
  3. insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD-MON-YYYY HH:MI:SS') , 12345);
  4. insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD-MON-YYYY HH:MI:SS') , 12346);
  5. insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD-MON-YYYY HH:MI:SS') , 12347);
  6. insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD-MON-YYYY HH:MI:SS') , 42345);
  7. insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD-MON-YYYY HH:MI:SS') , 42346);
  8. insert into time1 values (to_date ('01-JAN-2022 03:35:00', 'DD-MON-YYYY HH:MI:SS') , 42347);
  9. insert into time1 values (to_date ('01-JAN-2022 03:40:00', 'DD-MON-YYYY HH:MI:SS') , 42348);
  10. insert into time1 values (to_date ('01-JAN-2022 10:40:00', 'DD-MON-YYYY HH:MI:SS') , 10348);
  11. insert into time1 values (to_date ('01-JAN-2022 10:42:00', 'DD-MON-YYYY HH:MI:SS') , 10349);
  12. insert into time1 values (to_date ('01-JAN-2022 10:43:00', 'DD-MON-YYYY HH:MI:SS') , 11348);
  14. COMMIT;

输出如下所示：-

  1. | 小时      | 计数    |
  2. |:--------  |:-------|
  3. |00:15:00  |3     |
  4. |01:15:00  |0     |
  5. |02:15:00  |0     |
  6. |03:15:00  |0     |
  7. |04:15:00  |4     |
  8. |05:15:00  |0     |
  9. |06:15:00  |0     |
  10. |07:15:00  |0     |
  11. |08:15:00  |0     |
  12. |09:15:00  |0     |
  13. |10:15:00  |0     |
  14. |11:15:00  |3     |
  15. |..
  16. |...
  17. |23:15:00  |0     |
英文:

I require to fetch the count of PRODUCT_ID between 00:15:00 and 01:15:00 and subsequentially for any date range.

Example Scripts:-

My DB structure and data is as follows.

  1. CREATE TABLE time1 (cr_date date , product_id number );
  3. insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12345);
  4. insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12346);
  5. insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12347);
  6. insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42345);
  7. insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42346);
  8. insert into time1 values (to_date ('01-JAN-2022 03:35:00', 'DD_MON-YYYY HH:MI:SS') , 42347);
  9. insert into time1 values (to_date ('01-JAN-2022 03:40:00', 'DD_MON-YYYY HH:MI:SS') , 42348);
  10. insert into time1 values (to_date ('01-JAN-2022 10:40:00', 'DD_MON-YYYY HH:MI:SS') , 10348);
  11. insert into time1 values (to_date ('01-JAN-2022 10:42:00', 'DD_MON-YYYY HH:MI:SS') , 10349);
  12. insert into time1 values (to_date ('01-JAN-2022 10:43:00', 'DD_MON-YYYY HH:MI:SS') , 11348);
  14. COMMIT;

Output is required to be as below:-

  1. | hours   | count |
  2. |:------  |:------|
  3. |00:15:00 |3|
  4. |01:15:00 |0|
  5. |02:15:00 |0|
  6. |03:15:00 |0|
  7. |04:15:00 |4|
  8. |05:15:00 |0|
  9. |06:15:00 |0|
  10. |07:15:00 |0|
  11. |08:15:00 |0|
  12. |09:15:00 |0|
  13. |10:15:00 |0|
  14. |11:15:00 |3|
  15. |..
  16. |...
  17. |23:15:00 |0|

答案1

得分: 1

你最初发布的示例数据基本无用，没有涉及时间组件。

在我的一个表中，有一个 datum 列，值看起来像这样：

这是查询：

  • 在 fmin CTE 中创建 24 行（00:15, 01:15, ... 23:15）
  • 在 datum 上进行联接到前一个15分钟的“舍入”值（无论是在同一小时内还是在前一个小时内 - 取决于分钟）

所以：

结果：

  1.  C_TIM        CNT
  2. ----- ----------
  3. 00:15          0
  4. 01:15          0
  5. 02:15          0
  6. 03:15          0
  7. 04:15          0
  8. 05:15          0
  9. 06:15          0
  10. 07:15          2
  11. 08:15         10
  12. 09:15          1
  13. 10:15         14
  14. 11:15          6
  15. 12:15         10
  16. 13:15         38
  17. 14:15          5
  18. 15:15          0
  19. 16:15          0
  20. 17:15          0
  21. 18:15          0
  22. 19:15          0
  23. 20:15          0
  24. 21:15          0
  25. 22:15          0
  26. 23:15          0
  28. 24 rows selected.
英文:

Sample data you (initially) posted is pretty much useless, there's no time component involved.

In one of my tables, there's a datum column and values look like this:

  1. SQL> SELECT id, TO_CHAR (datum, 'hh24:mi') hrs FROM obr WHERE rownum <= 10;
  3.         ID HRS
  4. ---------- -----
  5.      21547 08:41
  6.      21541 08:17
  7.      21563 09:03
  8.      21614 10:46
  9.      21618 11:01
  10.      21620 11:04
  11.      21622 11:05
  12.      21626 11:10
  13.      21629 11:14
  14.      21642 13:35
  16. 10 rows selected.
  18. SQL>

This is query which

  • in fmin CTE creates 24 rows (00:15, 01:15, ... 23:15)
  • join is done on datum "rounded" to previous 15-minute value (whether in the same hour, or in previous hour - depends on minutes)

So:

  1. SQL> WITH
  2.   2     fmin
  3.   3     AS
  4.   4        (    SELECT TRUNC (SYSDATE) + (LEVEL - 1) / 24 + INTERVAL '15' MINUTE c_time
  5.   5               FROM DUAL
  6.   6         CONNECT BY LEVEL <= 24)
  7.   7    SELECT TO_CHAR (f.c_time, 'hh24:mi') c_time, COUNT (z.id) cnt
  8.   8      FROM fmin f
  9.   9           LEFT JOIN obr z
  10.  10              ON TO_CHAR (f.c_time, 'hh24:mi') =
  11.  11                 TO_CHAR (
  12.  12                      TRUNC (datum, 'hh24')
  13.  13                    + CASE
  14.  14                         WHEN TO_NUMBER (TO_CHAR (datum, 'mi')) >= 15
  15.  15                         THEN
  16.  16                            INTERVAL '15' MINUTE
  17.  17                         WHEN TO_NUMBER (TO_CHAR (datum, 'mi')) < 15
  18.  18                         THEN
  19.  19                            INTERVAL '-45' MINUTE
  20.  20                      END,
  21.  21                    'hh24:mi')
  22.  22  GROUP BY TO_CHAR (f.c_time, 'hh24:mi')
  23.  23  ORDER BY 1;

Result:

  1. C_TIM        CNT
  2. ----- ----------
  3. 00:15          0
  4. 01:15          0
  5. 02:15          0
  6. 03:15          0
  7. 04:15          0
  8. 05:15          0
  9. 06:15          0
  10. 07:15          2
  11. 08:15         10
  12. 09:15          1
  13. 10:15         14
  14. 11:15          6
  15. 12:15         10
  16. 13:15         38
  17. 14:15          5
  18. 15:15          0
  19. 16:15          0
  20. 17:15          0
  21. 18:15          0
  22. 19:15          0
  23. 20:15          0
  24. 21:15          0
  25. 22:15          0
  26. 23:15          0
  28. 24 rows selected.
  30. SQL>

答案2

得分: 0

根据时间来计算数量是不可能的，因为你只在CR_DATE列中存储了日期。

即使你将date转换为timestamp，时间始终会被设置为默认值，即00:00:00.000000或12:00:00.000000。

要实现这个目标，你需要像下面这样重新创建你的表：

  1. CREATE TABLE time1 (cr_date timestamp, product_id number );

然后，你需要像下面的示例一样将实际的timestamp插入表中：

  1. INSERT INTO TIME1 (CR_DATE,PRODUCT_ID) VALUES (to_timestamp('01-01-22 14:32:62.258152','DD-MM-RR HH24:MI:SS.FF'),12345);

这里是to_timestamp的文档。

在此之后，你可以根据时间查询，就像你的问题中所描述的那样。

  1. SELECT TRUNC(CR_DATE,'HH'), count(product_id) 
  2. FROM TIME1 
  3. GROUP BY TRUNC(CR_DATE,'HH');

这里是TRUNC()函数的文档。

从上面的查询中可能会得到以下结果。

  1. TRUNC(CR_DATE,'HH')   COUNT(product_id)
  2. ------------------- ----------
  3. 2022-01-01 09:00:00        748
  4. 2022-01-01 16:00:00         24
  5. 2022-01-01 17:00:00         12
  6. 2022-01-01 22:00:00        737
  7. 2022-01-01 23:00:00        182
  8. 2022-01-01 20:00:00         16
  9. 2022-01-01 21:00:00        293
  10. 2022-01-01 22:00:00        610

如果你发现任何问题，请在下面评论以进一步讨论。

英文:

You wont get count based on time as you are storing only date in CR_DATE column.

Even if you convert date to timestamp, the time will always be set to default value i.e., 00:00:00.000000 or 12:00:00.000000.

To achieve this, you have to re-create your table like below :

  1. CREATE TABLE time1 (cr_date timestamp, product_id number );

And then, you have to insert actual timestamp into the table like the one example below.

  1. INSERT INTO TIME1 (CR_DATE,PRODUCT_ID) VALUES (to_timestamp('01-01-22 14:32:62.258152','DD-MM-RR HH24:MI:SS.FF'),12345);

Here is the documentations for to_timestamp.

After this, you can query based on time as per your question.

  1. SELECT TRUNC(CR_DATE,'HH'), count(product_id) 
  2. FROM TIME1 
  3. GROUP BY TRUNC(CR_DATE,'HH');

Here is the documentation for TRUNC() function.

The following result can be possibly obtained from above query.

  1. TRUNC(CR_DATE,'HH')   COUNT(product_id)
  2. ------------------- ----------
  3. 2022-01-01 09:00:00        748
  4. 2022-01-01 16:00:00         24
  5. 2022-01-01 17:00:00         12
  6. 2022-01-01 22:00:00        737
  7. 2022-01-01 23:00:00        182
  8. 2022-01-01 20:00:00         16
  9. 2022-01-01 21:00:00        293
  10. 2022-01-01 22:00:00        610

If you find any issue, please comment below for further discussion.

huangapple
  • 本文由 发表于 2023年6月2日 13:59:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/76387483.html
  • oracle
  • oracle12c
  • sql
匿名

发表评论

匿名网友

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

确定