24 hours a day every hour how statistics total advice on how to write SQL o thank you-CodePudding

24 hours a day every hour how statistics total advice on how to write SQL o thank you
Time is a number?
1
332 2
3 98
.
24 November

CodePudding user response:

There is a time field inside the table a ORDERTIME

CodePudding user response:

The table structure and sample data to stick out

CodePudding user response:

reference 1st floor cqwish16 response:

table there is a time field in a ORDERTIME

The table name
OrderList

Field
CreateTime name...
The 2014-02-14 12:11:22 test

CodePudding user response:

The select trunc (createTime), to_char (createTime, 'hh24), count (*) from orderList group by trunc (createTime), to_char (createTime,' hh24 ');

CodePudding user response:

The

reference 4 floor IceIsabel reply:

select trunc (createTime), to_char (createTime, 'hh24), count (*) from orderList group by trunc (createTime), to_char (createTime,' hh24 ');

Sorry may be I am not clear, check you this is all the data I as long as the data a day, and also queries from the empty like 2 o 'clock 0 0. 3 o 'clock query not to come out of your query data is empty. Thanks

CodePudding user response:

reference 5 floor cqwish16 reply:

Quote: refer to 4th floor IceIsabel response:

The select trunc (createTime), to_char (createTime, 'hh24), count (*) from orderList group by trunc (createTime), to_char (createTime,' hh24 ');

Sorry may be I am not clear, check you this is all the data I as long as the data a day, and also queries from the empty like 2 o 'clock 0 0. 3 o 'clock query not to come out of your query data is empty thank you

Want to find a day plus the where condition, as far as the data is empty to found out you can consider to build a table, 00 ~ 23 these values, left a connection

CodePudding user response:

 create table orderList (
CreateTime date default (sysdate), 
Name varchar2 (20) 
) 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
Insert into orderList (name) values (" aa "); 
Insert into orderList (name) values (" bb "); 
Insert into orderList (name) values (" cc "); 
Insert into orderList (name) values (" dd "); 
Insert into orderList (name) values (" ee "); 
Insert into orderList (name) values (' ff '); 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
With C_tab as (
The select to_char (sysdate, 'yyyy - mm - dd) | | "| | lpad atime (level, 2,' 0 ') from dual connect by level & lt;=24) 
The select b.a time, count (NVL (a.c reatetime, null)) from orderList a right join C_tab b on to_char (a.c reatetime, 'yyyy - mm - dd hh24')=b.a time 
Group by b.a time 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
Drop table orderList.

the New Year, my writing,

CodePudding user response:

The original poster can give me answers your question I met a few days didn't point idea can you add my QQ 1628288336

CodePudding user response:

 

- create a test table 
- circular insert test data 
Declare the n number. 
The begin 
N:=0; 
While n<50000 loop 
N:=n + 1; 
Insert into hour_test 
Select * from (
Select rownum rn, to_date (' 2017-01-01 ', 'yyyy - - dd hh: mm mi: ss') + rownum/24 add_hour from dual 
Connect by rownum<25) a 
Where a.r n=(select round (dbms_random value (1, 24)) from dual); 
End loop; 
commit; 
The end; 
- results of a query 
The select to_char (add_hour, 'hh24) and count (*) n the from hour_test 
Group by to_char (add_hour, 'hh24') 
The order by to_char (add_hour, 'hh24');

CodePudding user response:

http://blog.csdn.net/qq_28766327/article/details/77864672
My solution

CodePudding user response:

Why don't you export to excel table a sum statement is solved?

CodePudding user response:

Sorry, I understand the wrong!

CodePudding user response:

The

reference 11 floor weixin_40097177 reply:

why not export to excel table a sum statement is solved?

CodePudding user response:

The

reference 11 floor weixin_40097177 reply:

why not export to excel table a sum statement is solved?

Like you mean need to reflect in the excel table, with a sum total, the specific figure above, can write SQL? Online, etc., thank you

CodePudding user response:

references 9 f u010438126 response:

 

- create a test table 
- circular insert test data 
Declare the n number. 
The begin 
N:=0; 
While n<50000 loop 
N:=n + 1; 
Insert into hour_test 
Select * from (
Select rownum rn, to_date (' 2017-01-01 ', 'yyyy - - dd hh: mm mi: ss') + rownum/24 add_hour from dual 
Connect by rownum<25) a 
Where a.r n=(select round (dbms_random value (1, 24)) from dual); 
End loop; 
commit; 
The end; 
- results of a query 
The select to_char (add_hour, 'hh24) and count (*) n the from hour_test 
Group by to_char (add_hour, 'hh24') 
The order by to_char (add_hour, 'hh24'); 

nullnull