postgresql 按月份和年份分组的Postgres查询总计数[已关闭]

k4aesqcs 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(148)

已关闭。此问题需要details or clarity。目前不接受回答。
**要改进此问题吗？**通过editing this post添加详细信息并阐明问题。

9天前关闭
Improve this question的
下面是数据表-

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-仅按Pkt过滤，其中Pkt = 02。
2-按'del_no'分组（将NORTH和SOUTH视为一个完整的回合），然后按月和年统计所有回合。
3-根据环境分隔计数（例如，第1行和第3行是一个完整的回合，都有PROD和TEST环境，因此一个计数应在PROD/TEST环境中，其他计数也是如此）。
4-如果相同的del_no（H_00018）在另一个月开始并在下个月结束，则应在开始月份添加计数。
输出-

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     |

型
下面的查询不适用于第四个条件-

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";

型
https://dbfiddle.uk/FGIzl6hy的

postgresql

来源：https://stackoverflow.com/questions/77443712/postgres-query-total-counts-grouped-by-month-and-year

1条答案

按热度按时间

w8ntj3qf1#

您可以使用多个CTE：

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

字符串

第一个cte用于过滤掉任何具有不完整round的del_no。
第二个cte用于获取环境组合和每个del_no的开始月份
然后，我们按月份和环境进行汇总以获得计数。

Demo here

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

我来回答

postgresql 按月份和年份分组的Postgres查询总计数[已关闭]

1条答案

相关问题

热门标签

最新问答