以下是数据表--
del_no | Pkt | direction | Env | start_datetimestamp |
---------+---------+------------+-------+---------------------+
H_00002 | 02 | SOUTH | PROD | 2022-10-29 16:20:57 |
E20 | 20 | NORTH | PROD | 2022-10-30 16:41:37 |
H_00002 | 02 | NORTH | TEST | 2022-10-30 17:21:17 |
E20 | 20 | SOUTH | DEV | 2022-10-30 17:30:24 |
H_00004 | 02 | NORTH | PROD | 2022-10-30 16:52:48 |
H_00004 | 02 | SOUTH | PROD | 2022-10-30 19:03:36 |
H_00007 | 02 | NORTH | PROD | 2022-10-30 20:52:48 |
H_00007 | 02 | SOUTH | PROD | 2022-10-30 21:03:36 |
H_00015 | 02 | SOUTH | TEST | 2022-11-13 19:11:10 |
L 0013 | 13 | NORTH | PROD | 2022-11-14 20:06:46 |
H_00015 | 02 | NORTH | TEST | 2022-11-15 20:17:40 |
L0021 | 21 | SOUTH | TEST | 2022-11-15 20:56:18 |
H_00015 | 02 | NORTH | PROD | 2022-11-15 20:17:40 |
L0027 | 21 | SOUTH | DEV | 2022-11-30 20:56:18 |
H_00019 | 02 | NORTH | PROD | 2022-11-30 20:17:40 |
L0023 | 21 | SOUTH | TEST | 2022-11-30 20:56:18 |
H_00019 | 02 | SOUTH | TEST | 2022-11-30 20:17:40 |
L0025 | 21 | SOUTH | TEST | 2022-11-30 20:56:18 |
H_00019 | 02 | SOUTH | DEV | 2022-11-30 20:17:40 |
H_00018 | 02 | SOUTH | PROD | 2023-10-31 20:17:40 |
H_00018 | 02 | NORTH | PROD | 2023-11-02 03:17:40 |
H_00033 | 02 | SOUTH | PROD | 2023-10-31 20:17:40 |
H_00033 | 02 | NORTH | DEV | 2023-11-02 03:17:40 |
我想数一下以下条件下的子弹数-
1- Filter by Pkt where Pkt = 02 only.
2- Group by 'del_no' (consider NORTH and SOUTH one complete round), then counts all the rounds by monthly and yearly.
3- Segregate the counts based on Env. (e.g. 1st and 3rd row is one complete round and both has PROD and TEST env so that one count should be in PROD/TEST Env, same goes for other).
4- If same del_no(H_00018) starts on another month and end on next month so the count should be added in start month.
输出-
Month | Env | Counts |
--------+---------------+--------+
2022-10 | PROD/TEST | 1 |
2022-10 | PROD | 2 |
2022-11 | PROD/TEST | 1 |
2022-11 | PROD/TEST/DEV | 1 |
2023-10 | PROD | 1 |
2023-10 | PROD/DEV | 1 |
以下查询在第4个条件下不起作用-
select "Month",sum(rounds)"Counts","Env" from (
select del_no,
to_char(start_datetimestamp,'YYYY-MM') "Month",
least( count(*)filter(where direction='SOUTH')
,count(*)filter(where direction='NORTH')) rounds,
string_agg(distinct env,'/' order by env) "Env"
from tablename where pkt='2' group by del_no,2)
group by "Month","Env";