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

目录

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```