目录
场景是数据源按月计算的,但是想按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```
![](https://mutouzuo.oss-cn-hangzhou.aliyuncs.com/my/mudouzuo1.png)
标签:case,end,数据源,when,else,state,date,superset,商业智能
From: https://www.cnblogs.com/bigleft/p/18159838