【商业智能图表superset】数据源行变为列
目录
场景是数据源按月计算的，但是想按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)
```bash
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;```
如果要先判断是否存在，然后再转义，单个列需要这样：
```python
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```
