postgresql 使用过滤器和按月分组的计数的Postgres查询

83qze16e 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(133)

下面是数据表-

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 |

字符串
我想在下面的条件下计算回合数-
1-仅按Pkt过滤，其中Pkt = 02。
2-按'del_no'分组（将NORTH和SOUTH视为一个完整的回合），然后按月统计所有回合。
3-根据环境分隔计数（例如，第1行和第3行是一个完整的回合，都有PROD和TEST环境，因此一个计数应在PROD/TEST环境中，其他计数也是如此）
输出-

Month   |       Env     | Counts |
--------+---------------+--------+
 Oct    | PROD/TEST     |  1     |
 Oct    | PROD          |  2     |
 Nov    | PROD/TEST     |  1     |
 Nov    | PROD/TEST/DEV |  1     |

型
https://dbfiddle.uk/FGIzl6hy

postgresql

来源：https://stackoverflow.com/questions/77435939/postgres-query-using-filter-and-counts-grouped-by-month

2条答案

按热度按时间

mu0hgdu01#

select "Month", sum(rounds)"Counts", "Env" from
(   select del_no,
           to_char(start_datetimestamp,'Mon') "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";

字符串
| 月|计数|Env|
| --|--|--|
| Nov| 1 |开发/生产/测试|
| Nov| 1 |产品/测试|
| Oct| 2 |PROD|
| Oct| 1 |产品/测试|
db<>fiddle demo
1.默认情况下，Env在字段中按顺序排列。

Month的文本缩写使得它的可用性和排序不太好。一个date_trunc()将把10月份的所有内容标记为可用和可排序的日期2022-10-01，11月份标记为2022-11-01，这可以简化进一步的处理。

赞(0）回复(0）举报 2023-11-18

frebpwbc2#

您可以使用多个CTE，其中第一个CTE用于过滤掉任何具有不完整轮次的del_no，第二个和第三个CTE用于根据月份和环境聚合数据：

with cte as (
  select del_no
  from tableName
  where Pkt = '2'
  group by del_no
  having count(distinct direction) = 2
),
cte2 as (
  select distinct to_char(start_datetimestamp, 'YYYY-MM') as _Month, t.del_no, Env
  from cte c
  inner join tableName t on c.del_no = t.del_no
),
cte3 as (
  select _Month, del_no, string_agg(distinct env, '/') as Env
  from cte2
  group by _Month, del_no
)
select _Month, env, count(env) as Counts 
from cte3
group by _Month, env

字符串
Demo here

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 使用过滤器和按月分组的计数的Postgres查询

2条答案

相关问题

热门标签

最新问答