我需要在00:15:00和01:15:00之间获取PRODUCT_ID
的计数,并随后获取任何日期范围的计数.
示例脚本:-
我的数据库 struct 和数据如下.
CREATE TABLE time1 (cr_date date , product_id number );
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12345);
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12346);
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12347);
insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42345);
insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42346);
insert into time1 values (to_date ('01-JAN-2022 03:35:00', 'DD_MON-YYYY HH:MI:SS') , 42347);
insert into time1 values (to_date ('01-JAN-2022 03:40:00', 'DD_MON-YYYY HH:MI:SS') , 42348);
insert into time1 values (to_date ('01-JAN-2022 10:40:00', 'DD_MON-YYYY HH:MI:SS') , 10348);
insert into time1 values (to_date ('01-JAN-2022 10:42:00', 'DD_MON-YYYY HH:MI:SS') , 10349);
insert into time1 values (to_date ('01-JAN-2022 10:43:00', 'DD_MON-YYYY HH:MI:SS') , 11348);
COMMIT;
输出要求如下:-
| hours | count |
|:------ |:------|
|00:15:00 |3|
|01:15:00 |0|
|02:15:00 |0|
|03:15:00 |0|
|04:15:00 |4|
|05:15:00 |0|
|06:15:00 |0|
|07:15:00 |0|
|08:15:00 |0|
|09:15:00 |0|
|10:15:00 |0|
|11:15:00 |3|
|..
|...
|23:15:00 |0|