sql - 计算出现次数

Question

我有一个问题:

    SELECT DISTINCT departments.department AS dep, array_agg(deps_with_computers.list_of_computers) AS arr
  FROM departments
    LEFT JOIN
    (SELECT departments.department, stud_in_class.list_ids AS list_of_computers
    FROM departments LEFT JOIN
    (SELECT departments.course, array_to_string(array_agg(ids_with_computers.computers),', ') AS list_ids
    FROM departments
    LEFT JOIN
      (SELECT students.student_id AS id, array_to_string(array_agg(m.medium),', ') AS computers, students.course
       FROM students LEFT JOIN
      (SELECT computers.medium, have_got_computers.student_id FROM have_got_computers LEFT JOIN computers ON have_got_computers.medium_id = computers.medium_id) AS m
      ON students.student_id = m.student_id
        GROUP BY students.student_id) AS ids_with_computers

        ON departments.course = ids_with_computers.course GROUP BY departments.course) AS stud_in_class
     ON departments.course = stud_in_class.course GROUP BY departments.department, stud_in_class.list_ids)
  AS deps_with_computers
ON departments.department = deps_with_computers.department
GROUP BY departments.department);

它返回我

dep     |  arr
----------------
somthing| {element}
nextdep | {e1, e2, e4, e7}
nextdep2| {e1, e1, e4, e7, e1, e4}

我想要这样的结果:

dep     | occurencies               |sum
----------------------------------------
somthing| element: 1                |1
nextdep | e1:1, e2: 1, e4: 1, e7: 1 |4
nextdep2| e1:3  e4: 2, e7: 1        |6

在 postgresql 中有什么简单的方法可以做到这一点吗？

Answer 1

如果我没理解错的话，你可以设法得到一个表(或 View )，其中包含 department 和 element 的行，并且你想要分组的行数按部门和元素，然后将结果与每个部门的总和相加。

您可以通过计算按department, element 分组的行来实现第一部分。要实现示例中所示的第二部分，您可以构建一个 JSON 值 ( json_object_agg is only available from PostgreSQL 9.4 onwards )。

WITH items (id, dep, element) AS (
    VALUES (1, 'dep1', 'E1'),
           (2, 'dep1', 'E2'),
           (3, 'dep1', 'E2'),
           (4, 'dep1', 'E2'),
           (5, 'dep1', 'E3'),
           (6, 'dep1', 'E3'),

           (7, 'dep2', 'E1'),
           (8, 'dep2', 'E1'),
           (9, 'dep2', 'E3'),

           (10, 'dep3', 'E1'),
           (11, 'dep3', 'E2'),
           (12, 'dep3', 'E4'),
           (13, 'dep3', 'E4'),
           (14, 'dep3', 'E4')
),
element_counts_per_department AS (
   SELECT dep, element, COUNT(id) AS element_count
   FROM items
   GROUP BY dep, element ORDER BY dep, element
)
SELECT dep,
    json_object_agg(element, element_count),
    SUM(element_count)
FROM element_counts_per_department
GROUP BY dep ORDER BY dep

中间查询 (element_counts_per_department) 将如下所示:

|  dep | element | element_count |
|------|---------|---------------|
| dep1 |      E1 |             1 |
| dep1 |      E2 |             3 |
| dep1 |      E3 |             2 |
| dep2 |      E1 |             2 |
| dep2 |      E3 |             1 |
| dep3 |      E1 |             1 |
| dep3 |      E2 |             1 |

最终的聚合将如下所示:

|  dep |                                  |     |
|------|----------------------------------|-----|
| dep1 | { "E1" : 1, "E2" : 3, "E3" : 2 } |   6 |
| dep2 | { "E1" : 2, "E3" : 1 }           |   3 |
| dep3 | { "E1" : 1, "E2" : 1, "E4" : 3 } |   5 |