如何计算重复行？

Question

我希望能够从包含两个整数值的元组创建直方图。

这是查询：

 SELECT temp.ad_id, temp.distance  as hits FROM ( 
 'UNION ALL .join(cupound_query)' # python
) as temp GROUP BY temp.ad_id,temp.distance 

对于此输入：

(51, 5)
(51, 0)
(51, 3)
(51, 0)
(88, 2)
(88, 2)
(88, 2)
(84, 1)
(81, 9)

将是：

(88,2) : 3
(51,0) : 2
(51,3) : 1
(51,5) : 1
(84,1) : 1
(81,9) : 1

如何创建这些值的直方图？
换句话说，我如何计算一行中有多少次重复？

Answer 1

这个问题留下了解释的空间。此测试用例显示 2 个嵌套步骤：

CREATE TABLE tbl (ad_id int, distance int);
INSERT INTO tbl VALUES
  (510, 0), (956, 3), (823, 3), (880, 2)
, (523, 3), (467, 0), (843, 1), (816, 9)
, (533, 4), (721, 7), (288, 3), (900, 3)
, (526, 9), (750, 7), (302, 8), (463, 6)
, (742, 8), (804, 2), (62,  7), (880, 2)
, (523, 3), (467, 0), (843, 1), (816, 9)
, (533, 4), (721, 7), (288, 3), (900, 3)
, (526, 9), (750, 7), (302, 8), (816, 9)
, (533, 4), (721, 7), (288, 3), (900, 3)
, (533, 4), (721, 7), (288, 3), (396, 5)
;

每个值有多少个重复项？

SELECT ad_id, count(*) AS ct FROM tbl GROUP BY 1;

结果：

ad_id  | ct
-------+----
62     | 1
288    | 4
302    | 2
396    | 1
...

读取：ad_id 62 存在 1x，ad_id 288 存在 4x，...

如何计算行有重复项的次数？

SELECT ct, count(*) AS ct_ct
FROM  (SELECT ad_id, count(*) AS ct FROM tbl GROUP BY 1) sub
GROUP  BY 1
ORDER  BY 1;

结果：

 ct | ct_ct
----+-------
1   | 8
2   | 7
3   | 2
4   | 3

读取：出现 8 次“ad_id 是唯一的”，出现 7 次“2 行具有相同 ad_id”，...

>db>>小提琴此处

Answer 2

只需将 count(*) 添加到您的选择中即可：

SELECT temp.ad_id, temp.distance as hits, count(*)
....