第八章 分组和聚集

1. 基本描述

　　本章主要讲解如何对数据进行分组与聚类，以使用户在更高的粒度层次上与数据进行交互。

2. 基本样例

SELECT open_emp_id FROM account;

SELECT open_emp_id FROM account GROUP BY open_emp_id;

SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id;

SELECT open_emp_id, COUNT(*) how_many FROM account
GROUP BY open_emp_id HAVING COUNT(*) > 4;

SELECT MAX(avail_balance) max_balance, MIN(avail_balance) min_balance,
AVG(avail_balance) avg_balance, SUM(avail_balance) tot_balance,
COUNT(*) num_accounts FROM account WHERE product_cd = 'CHK';

SELECT product_cd, MAX(avail_balance) max_balance,
MIN(avail_balance) min_balance, AVG(avail_balance) avg_balance,
SUM(avail_balance) tot_balance, COUNT(*) num_accts FROM account GROUP BY product_cd;

SELECT account_id, open_emp_id FROM account ORDER BY open_emp_id;

SELECT COUNT(open_emp_id) FROM account;

SELECT COUNT(DISTINCT open_emp_id) FROM account;

SELECT MAX(pending_balance - avail_balance) max_uncleared FROM account;

SELECT product_cd, SUM(avail_balance) prod_balance FROM account GROUP BY product_cd;

SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance FROM account
GROUP BY product_cd, open_branch_id;

SELECT EXTRACT(YEAR FROM start_date) year, COUNT(*) how_many FROM employee
GROUP BY EXTRACT(YEAR FROM start_date);

SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance FROM account
GROUP BY product_cd, open_branch_id WITH ROLLUP;

SELECT product_cd, SUM(avail_balance) prod_balance FROM account WHERE status = 'ACTIVE' GROUP BY product_cd
HAVING SUM(avail_balance) >= 10000;

SELECT product_cd, SUM(avail_balance) prod_balance FROM account WHERE status = 'ACTIVE' GROUP BY product_cd
HAVING MIN(avail_balance) >= 1000 AND MAX(avail_balance) <= 10000;