需求描述上是这么定义的表里边有一个time时间字段,这里边的数据都是存的时间数据
例如现在表里边有3天的数据,需要把3天的1小时数据汇到一起,2小时数据汇到一起.....24小时汇总到一起。显示的结果如下kpi 1小时 2小时 3小时....24小时
k1 25 30 60 100上面列出的结果为kpi为k1,把3天的0点-1点的数据汇总为25,1点-2点的数据汇总为30 2点到3点的数据汇总为60请大家看看sql怎么写
例如现在表里边有3天的数据,需要把3天的1小时数据汇到一起,2小时数据汇到一起.....24小时汇总到一起。显示的结果如下kpi 1小时 2小时 3小时....24小时
k1 25 30 60 100上面列出的结果为kpi为k1,把3天的0点-1点的数据汇总为25,1点-2点的数据汇总为30 2点到3点的数据汇总为60请大家看看sql怎么写
kpi time qty
Ki 2009/7/23 01:00:00 1
ki 2009/7/23 01:05:00 2
Ki 2009/7/23 02:00:00 3
ki 2009/7/23 02:05:00 4select kpi
sum(to_char(time,'hh24'),'00',qty,0) “0-1点",
sum(to_char(time,'hh24'),'01',qty,0) “1-12点",
.
.
.
sum(to_char(time,'hh24'),'23',qty,0) “23-24点"
from table
group by kpi
group by to_char(e.time,'HH24')
order by to_char(e.time,'HH24') ;
2 (
3 SELECT TRUNC(SYSDATE) + DBMS_RANDOM.VALUE AS F_TIME,
4 ROUND(ROWNUM * DBMS_RANDOM.VALUE) AS F_VALUE
5 FROM DUAL
6 CONNECT BY ROWNUM < 25
7 ),
8 DIM AS
9 (
10 SELECT TRUNC(SYSDATE) + (ROWNUM - 1) / 24 AS D_TIME
11 FROM DUAL
12 CONNECT BY ROWNUM < 25
13 )
14 SELECT 'KPI',SUM(DECODE(B.D_HOUR,'00',A.SUM_VALUE,0)) AS "0-1 点",
15 SUM(DECODE(B.D_HOUR,'01',A.SUM_VALUE,0)) AS "1 -2 点",
16 SUM(DECODE(B.D_HOUR,'02',A.SUM_VALUE,0)) AS "2 -3 点",
17 SUM(DECODE(B.D_HOUR,'03',A.SUM_VALUE,0)) AS "3 -4 点",
18 SUM(DECODE(B.D_HOUR,'04',A.SUM_VALUE,0)) AS "4 -5 点",
19 SUM(DECODE(B.D_HOUR,'05',A.SUM_VALUE,0)) AS "5 -6 点",
20 SUM(DECODE(B.D_HOUR,'06',A.SUM_VALUE,0)) AS "6 -7 点",
21 SUM(DECODE(B.D_HOUR,'07',A.SUM_VALUE,0)) AS "7 -8 点",
22 SUM(DECODE(B.D_HOUR,'08',A.SUM_VALUE,0)) AS "8 -9 点",
23 SUM(DECODE(B.D_HOUR,'09',A.SUM_VALUE,0)) AS "9 -10点",
24 SUM(DECODE(B.D_HOUR,'10',A.SUM_VALUE,0)) AS "10-11点",
25 SUM(DECODE(B.D_HOUR,'11',A.SUM_VALUE,0)) AS "11-12点",
26 SUM(DECODE(B.D_HOUR,'12',A.SUM_VALUE,0)) AS "12-13点",
27 SUM(DECODE(B.D_HOUR,'13',A.SUM_VALUE,0)) AS "13-14点",
28 SUM(DECODE(B.D_HOUR,'14',A.SUM_VALUE,0)) AS "14-15点",
29 SUM(DECODE(B.D_HOUR,'15',A.SUM_VALUE,0)) AS "15-16点",
30 SUM(DECODE(B.D_HOUR,'16',A.SUM_VALUE,0)) AS "16-17点",
31 SUM(DECODE(B.D_HOUR,'17',A.SUM_VALUE,0)) AS "17-18点",
32 SUM(DECODE(B.D_HOUR,'18',A.SUM_VALUE,0)) AS "18-19点",
33 SUM(DECODE(B.D_HOUR,'19',A.SUM_VALUE,0)) AS "19-20点",
34 SUM(DECODE(B.D_HOUR,'20',A.SUM_VALUE,0)) AS "20-21点",
35 SUM(DECODE(B.D_HOUR,'21',A.SUM_VALUE,0)) AS "21-22点",
36 SUM(DECODE(B.D_HOUR,'22',A.SUM_VALUE,0)) AS "22-23点",
37 SUM(DECODE(B.D_HOUR,'23',A.SUM_VALUE,0)) AS "23-24点"
38 FROM
39 (SELECT TO_CHAR(F_TIME,'HH24') AS F_HOUR,SUM(F_VALUE) AS SUM_VALUE FROM FACT GROUP BY TO_CHAR(F_TIME,'HH24')) A,
40 (SELECT TO_CHAR(D_TIME,'HH24') AS D_HOUR FROM DIM GROUP BY TO_CHAR(D_TIME,'HH24')) B
41 WHERE A.F_HOUR(+) = B.D_HOUR
42 ;
'KPI' 0-1 点 1 -2 点 2 -3 点 3 -4 点 4 -5 点 5 -6 点 6 -7 点 7 -8 点 8 -9 点 9 -10点 10-11点 11-12点 12-13点 13-14点 14-15点 15-16点 16-17点 17-18点 18-19点 19-20点 20-21点 21-22点 22-23点 23-24点
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
KPI 31 0 13 4 0 0 12 14 11 5 0 1 15 0 8 0 2 9 0 0 0 12 0 6
SQL>
剩下的就是上面高手们写的那样了