【商业智能图表superset】数据源行变为列

矫情吗；* 2021-07-26 19:07 277阅读 0赞

**目录**

1、数据源为posgresql

2、数据源为clickhouse

--------------------

场景是数据源按月计算的，但是想按1号，2号，3号，的列来查看，这里要先汇总，按姓名group by，将行转换为列，数据源不同时，对时间的函数不一样。

## 1、数据源为posgresql ##

postgres的抽取时间中月的第几天：extract(day from update\_time)

SELECT person_no AS person_no,
           org_name AS org_name,
           extract(day from update_time),
           sum(case when extract(day from update_time) =1 then quota else 0 end) as day1,
    	   sum(case when extract(day from update_time) =2 then quota else 0 end) as day2,
    	   case when sum(case when extract(day from update_time) =1 then quota else 0 end) =3 then 'gd' else '' end as day1_str
    FROM public.tb_quota
    WHERE update_time >= '2020-08-31 00:00:00.000000'
      AND update_time < '2020-09-07 00:00:00.000000'
      group by person_no,org_name,update_time
    
    LIMIT 1000;

## 2、数据源为clickhouse ##

clickhouse的抽取时间中月的第几天：toDayOfMonth(date)

SELECT formatDateTime(date,'%Y-%m') AS month_str,
           person_name AS person_name,
           person_id AS person_id,
           sum(case
                   when toDayOfMonth(date) =10 then state
                   else 0
               end) AS day10,
           sum(case
                   when toDayOfMonth(date) =1 then state
                   else 0
               end) AS "1",
           sum(case
                   when toDayOfMonth(date) =11 then state
                   else 0
               end) AS "11"
    FROM pdms_pdmsdb.tb_model
    GROUP BY formatDateTime(date,'%Y-%m'),
             person_name,
             person_id
    ORDER BY day10 DESC
    LIMIT 1000;

如果要先判断是否存在，然后再转义，单个列需要这样：

case when has(groupArray(toDayOfMonth(date)),25)=0 then '' when sum(case when toDayOfMonth(date) =25 then (case when attendance_state is NULL then 100 else attendance_state end) else 0 end)=0 then '正常' 
    	 when sum(case when toDayOfMonth(date) =25 then (case when attendance_state is NULL then 100 else attendance_state end) else 0 end)=1 then '迟到' 
    	 when sum(case when toDayOfMonth(date) =25 then (case when attendance_state is NULL then 100 else attendance_state end) else 0 end)=2 then '早退' 
    	 when sum(case when toDayOfMonth(date) =25 then (case when attendance_state is NULL then 100 else attendance_state end) else 0 end)=3 then '旷工' 
    	 when sum(case when toDayOfMonth(date) =25 then (case when attendance_state is NULL then 100 else attendance_state end) else 0 end)=4 then '请假'
    	 when sum(case when toDayOfMonth(date) =25 then (case when attendance_state is NULL then 100 else attendance_state end) else 0 end)=100 then '未排班'	 
    else '' end