例如我现在需要对分数段来统计,每10分一个分数段,有表如下名字 分数
张三 76
李四 75
王五 64用case when来统计人数的话,结果是分数 人数
60-69 1
70-79 2而我需要的效果是分数 人数
0-9 0
10-19 0
.
.
.
60-69 1
70-79 2
.
.
90-99 0这只是我简化了的例子,实际的是以当前时间为准,每X秒(X=5秒,10秒...)向前推,作为一个时间段来分组统计.
张三 76
李四 75
王五 64用case when来统计人数的话,结果是分数 人数
60-69 1
70-79 2而我需要的效果是分数 人数
0-9 0
10-19 0
.
.
.
60-69 1
70-79 2
.
.
90-99 0这只是我简化了的例子,实际的是以当前时间为准,每X秒(X=5秒,10秒...)向前推,作为一个时间段来分组统计.
SELECT '0-9' "分数", SUM((CASE WHEN 分数>=0 AND 分数<=9 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '10-19' "分数", SUM((CASE WHEN 分数>=10 AND 分数<=19 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '20-29' "分数", SUM((CASE WHEN 分数>=20 AND 分数<=29 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '30-39' "分数", SUM((CASE WHEN 分数>=30 AND 分数<=39 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '40-49' "分数", SUM((CASE WHEN 分数>=40 AND 分数<=49 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '50-59' "分数", SUM((CASE WHEN 分数>=50 AND 分数<=59 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '60-69' "分数", SUM((CASE WHEN 分数>=60 AND 分数<=69 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '70-79' "分数", SUM((CASE WHEN 分数>=70 AND 分数<=79 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '80-89' "分数", SUM((CASE WHEN 分数>=80 AND 分数<=89 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '90-99' "分数", SUM((CASE WHEN 分数>=90 AND 分数<=99 THEN 1 ELSE 0 END))"人数"
FROM t_name
UNION
SELECT '100' "分数", SUM((CASE WHEN 分数=100 THEN 1 ELSE 0 END))"人数"
FROM t_name
select
'...到...' 分数,sum(case when 分数>... and 分数<... then 1 else 0 end) 人数 from tb
union all
'...到...' 分数,sum(case when 分数>... and 分数<... then 1 else 0 end) 人数 from tb
union all
...
union all
'...到...' 分数,sum(case when 分数>... and 分数<... then 1 else 0 end) 人数 from tb
when ...
from 表
select (0+10*t1.grade)||'-'||(9+10*t1.grade) 分数,nvl(cnt,0) 人数 from
(select (rownum -1) grade from dual connect by rownum<=10) t1,
(select floor(分数/10) grade ,count(1) cnt from table_test group by floor(分数/10))t2
where t1.grade=t2.grade(+)order by 分数
期待高手用case when 巧妙的解决方案……
select * from (
select case
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 0 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 1 * 5 then sysdate - (1 / 24 / 60) * 0 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 1 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 2 * 5 then sysdate - (1 / 24 / 60) * 1 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 2 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 3 * 5 then sysdate - (1 / 24 / 60) * 2 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 3 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 4 * 5 then sysdate - (1 / 24 / 60) * 3 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 4 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 5 * 5 then sysdate - (1 / 24 / 60) * 4 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 5 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 6 * 5 then sysdate - (1 / 24 / 60) * 5 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 6 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 7 * 5 then sysdate - (1 / 24 / 60) * 6 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 7 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 8 * 5 then sysdate - (1 / 24 / 60) * 7 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 8 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 9 * 5 then sysdate - (1 / 24 / 60) * 8 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 9 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 10 * 5 then sysdate - (1 / 24 / 60) * 9 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 10 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 11 * 5 then sysdate - (1 / 24 / 60) * 10 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 11 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 12 * 5 then sysdate - (1 / 24 / 60) * 11 * 5
end as 时间,sum(t.count_total) 交易量
from m_press_channel_history t
where to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 0 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 12 * 5
group by case when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 0 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 1 * 5 then sysdate - (1 / 24 / 60) * 0 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 1 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 2 * 5 then sysdate - (1 / 24 / 60) * 1 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 2 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 3 * 5 then sysdate - (1 / 24 / 60) * 2 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 3 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 4 * 5 then sysdate - (1 / 24 / 60) * 3 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 4 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 5 * 5 then sysdate - (1 / 24 / 60) * 4 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 5 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 6 * 5 then sysdate - (1 / 24 / 60) * 5 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 6 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 7 * 5 then sysdate - (1 / 24 / 60) * 6 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 7 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 8 * 5 then sysdate - (1 / 24 / 60) * 7 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 8 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 9 * 5 then sysdate - (1 / 24 / 60) * 8 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 9 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 10 * 5 then sysdate - (1 / 24 / 60) * 9 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 10 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 11 * 5 then sysdate - (1 / 24 / 60) * 10 * 5
when to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') <= sysdate - (1 / 24 / 60) * 11 * 5 and to_date(to_char(t.total_date, 'yyyy-mm-dd') || ' ' || t.total_time, 'yyyy-mm-dd hh24miss') >= sysdate - (1 / 24 / 60) * 12 * 5 then sysdate - (1 / 24 / 60) * 11 * 5
end
order by 时间
) tt