sql - 按另一列值分组并计数

我有一张桌子如下：CREATE TABLE public.test_table( "ID" serial PRIMARY KEY NOT NULL, "CID" integer NOT NULL, "SEG" integer NOT NULL, "DDN" character varying(3) NOT NULL)数据如下：ID CID SEG DDN1 1 1 "711"2 1 2 "800"3 1 3 "124"4 2 1 "711"5 3 1 "711"6 3 2 "802"7 4 1 "799"8 5 1 "799"9 5 2 "804"10 6 1 "799"我需要按CID列对这些数据进行分组，得到的列计数取决于DDN column s的第一个值，但计数必须给我两个不同的信息，如果它大于1或不大于1。如果不能解释清楚，我真的很抱歉。让我告诉你我需要什么。。DDN END TRA711 1 2799 2 1如您所见，DDN:711有1个单次计数记录（ID:4）。这是结束栏。但是2次有多个SEG计数（ID:1to3和ID:5to6）。这是特拉纵队。我不知道在group子句中应该是哪个列！我的解决方案：刚找到一个解决方案如下WITH x AS ( SELECT (SELECT t1."DDN" FROM public.test_table AS t1 WHERE t1."CID"=t."CID" AND t1."SEG"=1) AS ddn, COUNT("CID") AS seg_count FROM public.test_table AS t GROUP BY "CID")SELECT ddn, COUNT(seg_count) AS "TOTAL", SUM(CASE WHEN x.seg_count=1 THEN 1 ELSE 0 END) as "END", SUM(CASE WHEN x.seg_count>1 THEN 1 ELSE 0 END) as "TRA"FROM xGROUP BY ddn; 最佳答案 同等、更快的查询：SELECT "DDN" , COUNT(*) AS "TOTAL" , COUNT(*) FILTER (WHERE seg_count = 1) AS "END" , COUNT(*) FILTER (WHERE seg_count > 1) AS "TRA"FROM ( SELECT DISTINCT ON ("CID") "DDN" -- assuming min "SEG" is always 1 , COUNT(*) OVER (PARTITION BY "CID") AS seg_count FROM test_table ORDER BY "CID", "SEG" ) subGROUP BY "DDN";分贝小提琴here笔记：CTE通常速度较慢，只应在Postgres中需要时使用。这相当于问题中的查询，假设每个“CID”的最小“SEG”始终为1，因为此查询返回具有最小“SEG”的行，而查询返回具有"SEG" = 1的行。通常，您会希望“第一个”段和我的查询更可靠地实现这一要求，但这个问题并不清楚。COUNT(*)略快于COUNT(column)和等效值，但不涉及空值（此处适用）。相关：PostgreSQL: running count of rows for a query 'by minute'关于DISTINCT ON：Select first row in each GROUP BY group?aggregateFILTER语法需要Postgres 9.4+：SQL count if columns