求如何实现行转列，并按时间统计

ajdkjalj 2018-10-30 11:29:50 


create table SC

(

 mtype varchar(10),

 worktime DATE

)



insert into SC VALUES('SD01',TO_DATE('2017-02-28 08:40:00','yyyy-mm-dd hh24:mi:ss'))

insert into SC VALUES('GH1',TO_DATE('2017-02-28 08:50:00','yyyy-mm-dd hh24:mi:ss'))

insert into SC VALUES('SD01',TO_DATE('2017-02-28 08:50:00','yyyy-mm-dd hh24:mi:ss'))

insert into SC VALUES('VS02',TO_DATE('2017-02-28 15:00:00','yyyy-mm-dd hh24:mi:ss'))

insert into SC VALUES('UY02',TO_DATE('2017-02-28 20:40:00','yyyy-mm-dd hh24:mi:ss'))

insert into SC VALUES('SD01',TO_DATE('2017-02-28 21:00:00','yyyy-mm-dd hh24:mi:ss'))

insert into SC VALUES('VS02',TO_DATE('2017-03-02 08:40:00','yyyy-mm-dd hh24:mi:ss'))


能够根据日期按时间统计从早上8:30到晚上21:30，每个小时统计一次,还有mtype内容是不定的，还有其他的类型，比如2月28号的结果像这样
time SD01 GH1 VS02 UY02 VS02
8:30-9:30 2 1
9:30-10:30
....
14:30-15:30 1 1
...
19:30-20:30
20:30-21:30 1 1
...全文
164 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
nayi_224 2018-11-01
  • 打赏
  • 举报
 回复
用动态创建视图的写了一个，也可以改成游标之类的。 

create or replace procedure test20181101 is 
    pivot_sql_text varchar2(30000);
    select_sql_text varchar2(30000);
begin

select to_char(wm_concat('''' || t1.mtype || '''')) pivot_sql_text ,
       to_char(wm_concat('count(decode(t2."''' || t1.mtype ||
                         '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text into pivot_sql_text, 
                                                                                                    select_sql_text
  from (select distinct v1.mtype mtype from sc v1) t1
 order by t1.mtype;
 
execute immediate 
'
create or replace view view_test20181101 as 
with tab1 as (
select 60 * 60 * (7.5 + level) st,
       60 * 60 * (8.5 + level) ed
  from dual
connect by level <= 13
),
tab2 as (
select*from sc t1
pivot(count(t1.mtype) for mtype in (' || pivot_sql_text || '))
)
select ' || select_sql_text || '
       to_char(trunc(sysdate) + t1.st / 86400, ''fmhh24:mi'') || ''-'' ||
       to_char(trunc(sysdate) + t1.ed / 86400, ''fmhh24:mi'') ttttt
  from tab1 t1, tab2 t2
where t1.st <= to_number(to_char(t2.worktime(+), ''sssss''))
  and t1.ed > to_number(to_char(t2.worktime(+), ''sssss''))
group by t1.st, t1.ed
order by t1.st
'
;
  
end test20181101;
调用 

begin
  test20181101;
end;

select*from view_test20181101;
nayi_224 2018-11-01
  • 打赏
  • 举报
 回复
引用 10 楼 ajdkjalj 的回复:
[quote=引用 9 楼 nayi_224 的回复:] 用动态创建视图的写了一个，也可以改成游标之类的。 

create or replace procedure test20181101 is 
    pivot_sql_text varchar2(30000);
    select_sql_text varchar2(30000);
begin

select to_char(wm_concat('''' || t1.mtype || '''')) pivot_sql_text ,
       to_char(wm_concat('count(decode(t2."''' || t1.mtype ||
                         '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text into pivot_sql_text, 
                                                                                                    select_sql_text
  from (select distinct v1.mtype mtype from sc v1) t1
 order by t1.mtype;
 
execute immediate 
'
create or replace view view_test20181101 as 
with tab1 as (
select 60 * 60 * (7.5 + level) st,
       60 * 60 * (8.5 + level) ed
  from dual
connect by level <= 13
),
tab2 as (
select*from sc t1
pivot(count(t1.mtype) for mtype in (' || pivot_sql_text || '))
)
select ' || select_sql_text || '
       to_char(trunc(sysdate) + t1.st / 86400, ''fmhh24:mi'') || ''-'' ||
       to_char(trunc(sysdate) + t1.ed / 86400, ''fmhh24:mi'') ttttt
  from tab1 t1, tab2 t2
where t1.st <= to_number(to_char(t2.worktime(+), ''sssss''))
  and t1.ed > to_number(to_char(t2.worktime(+), ''sssss''))
group by t1.st, t1.ed
order by t1.st
'
;
  
end test20181101;
调用 

begin
  test20181101;
end;

select*from view_test20181101;
我的oracle又没有wm_concat这个函数[/quote] 新版的好像要用系统脚本自己建一个。或者换成listagg 
create or replace procedure test20181101 is 
    pivot_sql_text varchar2(30000);
    select_sql_text varchar2(30000);
begin

select listagg('''' || t1.mtype || '''', ',') within group(order by t1.mtype) ,
       listagg('count(decode(t2."''' || t1.mtype ||
               '''", 0, null, null, null, 1)) ' || t1.mtype, ',') within group(order by t1.mtype) || ','  into pivot_sql_text, 
                                                                                                    select_sql_text
  from (select distinct v1.mtype mtype from sc v1) t1
 ;
 
execute immediate 
'
create or replace view view_test20181101 as 
with tab1 as (
select 60 * 60 * (7.5 + level) st,
       60 * 60 * (8.5 + level) ed
  from dual
connect by level <= 13
),
tab2 as (
select*from sc t1
pivot(count(t1.mtype) for mtype in (' || pivot_sql_text || '))
)
select ' || select_sql_text || '
       to_char(trunc(sysdate) + t1.st / 86400, ''fmhh24:mi'') || ''-'' ||
       to_char(trunc(sysdate) + t1.ed / 86400, ''fmhh24:mi'') ttttt
  from tab1 t1, tab2 t2
where t1.st <= to_number(to_char(t2.worktime(+), ''sssss''))
  and t1.ed > to_number(to_char(t2.worktime(+), ''sssss''))
group by t1.st, t1.ed
order by t1.st
'
;

  
end test20181101;
ajdkjalj 2018-11-01
  • 打赏
  • 举报
 回复
引用 9 楼 nayi_224 的回复:
用动态创建视图的写了一个，也可以改成游标之类的。



create or replace procedure test20181101 is 

    pivot_sql_text varchar2(30000);

    select_sql_text varchar2(30000);

begin



select to_char(wm_concat('''' || t1.mtype || '''')) pivot_sql_text ,

       to_char(wm_concat('count(decode(t2."''' || t1.mtype ||

                         '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text into pivot_sql_text, 

                                                                                                    select_sql_text

  from (select distinct v1.mtype mtype from sc v1) t1

 order by t1.mtype;

 

execute immediate 

'

create or replace view view_test20181101 as 

with tab1 as (

select 60 * 60 * (7.5 + level) st,

       60 * 60 * (8.5 + level) ed

  from dual

connect by level <= 13

),

tab2 as (

select*from sc t1

pivot(count(t1.mtype) for mtype in (' || pivot_sql_text || '))

)

select ' || select_sql_text || '

       to_char(trunc(sysdate) + t1.st / 86400, ''fmhh24:mi'') || ''-'' ||

       to_char(trunc(sysdate) + t1.ed / 86400, ''fmhh24:mi'') ttttt

  from tab1 t1, tab2 t2

where t1.st <= to_number(to_char(t2.worktime(+), ''sssss''))

  and t1.ed > to_number(to_char(t2.worktime(+), ''sssss''))

group by t1.st, t1.ed

order by t1.st

'

;

  

end test20181101;


调用



begin

  test20181101;

end;



select*from view_test20181101;


我的oracle又没有wm_concat这个函数
帅得烦。 2018-10-31
  • 打赏
  • 举报
 回复
假如你需要某个时间段的，加两个参数就行
ajdkjalj 2018-10-31
  • 打赏
  • 举报
 回复
引用 7 楼 weixin_43566217 的回复:
[quote=引用 6 楼 ajdkjalj 的回复:]
[quote=引用 5 楼 nayi_224 的回复:]
with tab1 as (

select 60 * 60 * (7.5 + level) st,

       60 * 60 * (8.5 + level) ed

  from dual

connect by level <= 13

),

tab2 as (

select*from sc t1

pivot(count(t1.mtype) for mtype in ('SD01', 'GH1', 'VS02', 'UY02'))

)

select to_char(trunc(sysdate) + t1.st / 86400, 'fmhh24:mi') || '-' ||

       to_char(trunc(sysdate) + t1.ed / 86400, 'fmhh24:mi') ttttt,

       count(decode(t2."'SD01'", 0, null, null, null, 1)) sd01,

       count(decode(t2."'GH1'", 0, null, null, null, 1)) gh1,

       count(decode(t2."'VS02'", 0, null, null, null, 1)) vs02,

       count(decode(t2."'UY02'", 0, null, null, null, 1)) uy02

  from tab1 t1, tab2 t2

where t1.st <= to_number(to_char(t2.worktime(+), 'sssss'))

  and t1.ed > to_number(to_char(t2.worktime(+), 'sssss'))

group by t1.st, t1.ed

order by t1.st

;


动态部分还是拿程序来拼吧
select to_char(wm_concat(distinct '''' || t1.mtype || '''')) pivot_sql_text,

       to_char(wm_concat(distinct 'count(decode(t2."''' || t1.mtype || '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text

from sc t1 order by t1.mtype;


还差一步，就是mtype是不固定的，你写死啦，有可能是其他的字段的[/quote]
老哥稳，后面这段当时就是感觉太麻烦了，不想拼，所以我就搞了个VALUE值为1来SUM[/quote]
我可以加分，我实在是看不太懂，更写不出来
帅得烦。 2018-10-31
  • 打赏
  • 举报
 回复
要统计成你要的结果的话，你需要每条数据后加一个VALUE值，每个都是1就行，用来计算数量。代码和结果如下： CREATE TABLE TMP_TEST_20181031_1 AS SELECT MTYPE,WORKTIME,1 VALUE FROM TMP_TEST_20181031; DECLARE SQL_TEXT VARCHAR(4000);COL_TEXT VARCHAR(4000); BEGIN SQL_TEXT:='SELECT LISTAGG(AA,'','') WITHIN GROUP(ORDER BY AA) FROM (SELECT DISTINCT ''''''''||MTYPE||'''''''' AA FROM TMP_TEST_20181031_1)'; EXECUTE IMMEDIATE SQL_TEXT INTO COL_TEXT; COMMIT; EXECUTE IMMEDIATE 'DROP TABLE TMP_TEST_20181031_2'; SQL_TEXT:=' CREATE TABLE TMP_TEST_20181031_2 AS SELECT * FROM (SELECT WORKTIME,MTYPE,VALUE FROM TMP_TEST_20181031_1) PIVOT(SUM(VALUE)FOR MTYPE IN ('||COL_TEXT||'))'; EXECUTE IMMEDIATE SQL_TEXT; END; SELECT * FROM TMP_TEST_20181031_2;
nayi_224 2018-10-31
  • 打赏
  • 举报
 回复
需要动态拼接sql，或者把行、列对调一下。
帅得烦。 2018-10-31
  • 打赏
  • 举报
 回复
引用 6 楼 ajdkjalj 的回复:
[quote=引用 5 楼 nayi_224 的回复:] 
with tab1 as (
select 60 * 60 * (7.5 + level) st,
       60 * 60 * (8.5 + level) ed
  from dual
connect by level <= 13
),
tab2 as (
select*from sc t1
pivot(count(t1.mtype) for mtype in ('SD01', 'GH1', 'VS02', 'UY02'))
)
select to_char(trunc(sysdate) + t1.st / 86400, 'fmhh24:mi') || '-' ||
       to_char(trunc(sysdate) + t1.ed / 86400, 'fmhh24:mi') ttttt,
       count(decode(t2."'SD01'", 0, null, null, null, 1)) sd01,
       count(decode(t2."'GH1'", 0, null, null, null, 1)) gh1,
       count(decode(t2."'VS02'", 0, null, null, null, 1)) vs02,
       count(decode(t2."'UY02'", 0, null, null, null, 1)) uy02
  from tab1 t1, tab2 t2
where t1.st <= to_number(to_char(t2.worktime(+), 'sssss'))
  and t1.ed > to_number(to_char(t2.worktime(+), 'sssss'))
group by t1.st, t1.ed
order by t1.st
;
动态部分还是拿程序来拼吧 
select to_char(wm_concat(distinct '''' || t1.mtype || '''')) pivot_sql_text,
       to_char(wm_concat(distinct 'count(decode(t2."''' || t1.mtype || '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text
from sc t1 order by t1.mtype;
还差一步，就是mtype是不固定的，你写死啦，有可能是其他的字段的[/quote] 老哥稳，后面这段当时就是感觉太麻烦了，不想拼，所以我就搞了个VALUE值为1来SUM
ajdkjalj 2018-10-31
  • 打赏
  • 举报
 回复
引用 5 楼 nayi_224 的回复:
with tab1 as (

select 60 * 60 * (7.5 + level) st,

       60 * 60 * (8.5 + level) ed

  from dual

connect by level <= 13

),

tab2 as (

select*from sc t1

pivot(count(t1.mtype) for mtype in ('SD01', 'GH1', 'VS02', 'UY02'))

)

select to_char(trunc(sysdate) + t1.st / 86400, 'fmhh24:mi') || '-' ||

       to_char(trunc(sysdate) + t1.ed / 86400, 'fmhh24:mi') ttttt,

       count(decode(t2."'SD01'", 0, null, null, null, 1)) sd01,

       count(decode(t2."'GH1'", 0, null, null, null, 1)) gh1,

       count(decode(t2."'VS02'", 0, null, null, null, 1)) vs02,

       count(decode(t2."'UY02'", 0, null, null, null, 1)) uy02

  from tab1 t1, tab2 t2

where t1.st <= to_number(to_char(t2.worktime(+), 'sssss'))

  and t1.ed > to_number(to_char(t2.worktime(+), 'sssss'))

group by t1.st, t1.ed

order by t1.st

;


动态部分还是拿程序来拼吧
select to_char(wm_concat(distinct '''' || t1.mtype || '''')) pivot_sql_text,

       to_char(wm_concat(distinct 'count(decode(t2."''' || t1.mtype || '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text

from sc t1 order by t1.mtype;


还差一步，就是mtype是不固定的，你写死啦，有可能是其他的字段的
nayi_224 2018-10-31
  • 打赏
  • 举报
 回复 
with tab1 as (
select 60 * 60 * (7.5 + level) st,
       60 * 60 * (8.5 + level) ed
  from dual
connect by level <= 13
),
tab2 as (
select*from sc t1
pivot(count(t1.mtype) for mtype in ('SD01', 'GH1', 'VS02', 'UY02'))
)
select to_char(trunc(sysdate) + t1.st / 86400, 'fmhh24:mi') || '-' ||
       to_char(trunc(sysdate) + t1.ed / 86400, 'fmhh24:mi') ttttt,
       count(decode(t2."'SD01'", 0, null, null, null, 1)) sd01,
       count(decode(t2."'GH1'", 0, null, null, null, 1)) gh1,
       count(decode(t2."'VS02'", 0, null, null, null, 1)) vs02,
       count(decode(t2."'UY02'", 0, null, null, null, 1)) uy02
  from tab1 t1, tab2 t2
where t1.st <= to_number(to_char(t2.worktime(+), 'sssss'))
  and t1.ed > to_number(to_char(t2.worktime(+), 'sssss'))
group by t1.st, t1.ed
order by t1.st
;
动态部分还是拿程序来拼吧 
select to_char(wm_concat(distinct '''' || t1.mtype || '''')) pivot_sql_text,
       to_char(wm_concat(distinct 'count(decode(t2."''' || t1.mtype || '''", 0, null, null, null, 1)) ' || t1.mtype)) || ',' select_sql_text
from sc t1 order by t1.mtype;
ajdkjalj 2018-10-31
  • 打赏
  • 举报
 回复
引用 3 楼 weixin_43566217 的回复:
假如你需要某个时间段的，加两个参数就行

客户是想要个时间段的，结果就像我开始列的那样，你这个结果具体到每个时间点了，我有个sqlserver的实现方法，但是我对oracle不熟悉，不知道怎么转换，如果能转换过来也行，代码如下


if OBJECT_ID('tempdb..#tempP') is not null

drop table #tempP

CREATE TABLE #tempP(mtype varchar(10),worktime DATETIME)

insert into #tempP

select 'SD01' ,'2017-02-28 08:10:00.000' UNION ALL

select 'GH1' ,'2017-02-28 10:00:00.000' UNION ALL

select 'SD01' ,'2017-02-28 14:40:00.000' UNION ALL

select 'VS02' ,'2017-02-28 15:00:00.000' UNION ALL

select 'UY02' ,'2017-02-28 20:00:00.000' UNION ALL

select 'VS02' ,'2017-03-28 08:40:00.000' 



DECLARE @START_TIME TIME

DECLARE @END_TIME TIME

DECLARE @SQL VARCHAR(MAX)



SET @START_TIME='08:30'

SET @END_TIME='21:30'



;WITH CTE_1

AS

(SELECT A.NUMBER,

CASE WHEN NUMBER<>0 THEN DATEADD(MINUTE,1,DATEADD(HOUR,NUMBER,START_TIME)) ELSE DATEADD(HOUR,NUMBER,START_TIME) END AS START_TIME,

DATEADD(HOUR,NUMBER+1,START_TIME) AS END_TIME

FROM MASTER.DBO.spt_values A

JOIN

(SELECT DATEDIFF(HOUR,@START_TIME,@END_TIME) AS QTY,@START_TIME AS START_TIME) AS B ON A.NUMBER<=B.QTY-1

WHERE TYPE='P'),



CTE_2

AS

(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+'-'+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,

mtype

FROM CTE_1 A

LEFT JOIN (SELECT * from #tempP WHERE CONVERT(VARCHAR(10),WORKTIME,23)='2017-03-28') B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME

)



SELECT @SQL=ISNULL(@SQL+',','')+'SUM(CASE WHEN MTYPE='''+MTYPE+''' THEN 1 ELSE 0 END) AS '+MTYPE

FROM

(SELECT MTYPE FROM #tempP GROUP BY MTYPE ) AS A



SET @SQL='

DECLARE @START_TIME TIME

DECLARE @END_TIME TIME



SET @START_TIME=''08:30''

SET @END_TIME=''21:30''



;WITH CTE_1

AS

(SELECT A.NUMBER,

CASE WHEN NUMBER<>0 THEN DATEADD(MINUTE,1,DATEADD(HOUR,NUMBER,START_TIME)) ELSE DATEADD(HOUR,NUMBER,START_TIME) END AS START_TIME,

DATEADD(HOUR,NUMBER+1,START_TIME) AS END_TIME

FROM MASTER.DBO.spt_values A

JOIN

(SELECT DATEDIFF(HOUR,@START_TIME,@END_TIME) AS QTY,@START_TIME AS START_TIME) AS B ON A.NUMBER<=B.QTY-1

WHERE TYPE=''P''),



CTE_2

AS

(SELECT CONVERT(VARCHAR(5),A.START_TIME,108)+''-''+CONVERT(VARCHAR(5),A.END_TIME,108) AS PERIOD,

mtype

FROM CTE_1 A

LEFT JOIN (SELECT * from #tempP WHERE CONVERT(VARCHAR(10),WORKTIME,23)=''2017-04-28'') B ON CONVERT(VARCHAR(10),B.WORKTIME,108) BETWEEN A.START_TIME AND A.END_TIME

)



SELECT PERIOD,'+@SQL+' FROM CTE_2 GROUP BY PERIOD order by PERIOD'



EXEC(@SQL)

SQL行转列、列转行
这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。 一、整理测试数据 create table wyc_test( id int(32) not null auto_...
pg库实现sql行转列
这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。 group by + sum + case when实现行转列： SELECT A.ORG_ID, A.tv_type_count,...
mysql 行转列 （带日期）
从网上找了很多行转列的。基本都是2行的行转列。不带日期分组的。 借鉴了另一个哥们的文章，实现了自己想要的结果，写出来大家可以参考。以后自己遇到同样情况，也可以有个备份 借鉴的地址为 ...
数据库：SQLServer 实现行转列、列转行用法笔记
在许多的互联网项目当中，报表开发是整个项目当中很重要的一个功能模块。其中会有一些比较复杂的报表统计需要行转列或者列转行的需求。今天给大家简单介绍一下在SQLServer当中如何使用PIV...
行转列，列转行
行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。 但是PIVOT 、UNPIVOT提供的语法比一系列复杂的SELECT...CASE 语句...
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章