DISTINCT

用来做去重，在pg中可以实现求分组中最大值.

常规写法：

info=# WITH ranked_employees AS (
info(#     SELECT
info(#         ROW_NUMBER() OVER (
info(#            PARTITION BY department ORDER BY salary DESC
info(#         ) AS rn,
info(#         *
info(#     FROM
info(#         employee
info(# )
info-# SELECT
info-#     *
info-# FROM
info-#     ranked_employees
info-# WHERE
info-#     rn = 1
info-# ORDER BY
info-#     department;
 rn | id |       name       |        department        | salary
----+----+------------------+--------------------------+--------
  1 | 30 | Sara Roberts     | Accounting               |  13845
  1 |  4 | Benjamin Brown   | Business Development     |   7386
  1 |  3 | Carolyn Carter   | Engineering              |   8366
  1 | 20 | Janet Hall       | Human Resources          |   2826
  1 | 14 | Chris Phillips   | Legal                    |   3706
  1 | 11 | Richard Bradley  | Marketing                |  11272
  1 |  2 | Richard Fox      | Product Management       |  13449
  1 | 25 | Evelyn Rodriguez | Research and Development |  10628
  1 | 17 | Benjamin Carter  | Sales                    |   6197
  1 | 24 | Jessica Elliott  | Services                 |  14542
  1 |  7 | Bonnie Robertson | Support                  |  12674
  1 |  8 | Jean Bailey      | Training                 |  13230

DISTINCT实现

info=# SELECT DISTINCT ON (department)
info-#     *
info-# FROM
info-#     employee
info-# ORDER BY
info-#     department,
info-#     salary DESC;
 id |       name       |        department        | salary
----+------------------+--------------------------+--------
 30 | Sara Roberts     | Accounting               |  13845
  4 | Benjamin Brown   | Business Development     |   7386
  3 | Carolyn Carter   | Engineering              |   8366
 20 | Janet Hall       | Human Resources          |   2826
 14 | Chris Phillips   | Legal                    |   3706
 11 | Richard Bradley  | Marketing                |  11272
  2 | Richard Fox      | Product Management       |  13449
 25 | Evelyn Rodriguez | Research and Development |  10628
 17 | Benjamin Carter  | Sales                    |   6197
 24 | Jessica Elliott  | Services                 |  14542
  7 | Bonnie Robertson | Support                  |  12674
  8 | Jean Bailey      | Training                 |  13230
(12 rows)

ARRAY_AGG

将分组中的非NULL值串联为一个字符串并且支持distinct:

SELECT
    department,
    ARRAY_AGG(DISTINCT salary) AS salaries
FROM
    employee
GROUP BY
    department;
        department        |   salaries
--------------------------+---------------
 Accounting               | {13845}
 Business Development     | {7386}
 Engineering              | {3052,8366}
 Human Resources          | {2826}
 Legal                    | {3706}
 Marketing                | {11272}
 Product Management       | {13449}
 Research and Development | {10628}
 Sales                    | {6197}
 Services                 | {10419,14542}
 Support                  | {12674}
 Training                 | {13230}
(12 rows)


类似函数：
listagg,不支持distinct:https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
group_concat:https://mariadb.com/kb/zh-cn/group_concat/