SQL统计分析语句涉及多表联查、分组求和分析、去重、数据对比，按照日期不同的角度进行按年月季周旬分析。(Oracle数据库)

1.多表联查并分组统计

SELECT d.department_name, AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name;

2.求两个表的差集

SELECT * FROM table_aEXCEPTSELECT * FROM table_b;

3.分组求和并筛选结果

SELECT text_id, COUNT(*) AS text_count, SUM(salary) AS text_salary FROM textGROUP BY text_id HAVING text_count > 3 AND text_salary > 10000;

4.分组求最大值并搭配子查询

SELECT department_id, MAX(salary) FROM employees WHERE salary < (SELECT AVG(salary) FROM employees) GROUP BY department_id;

5.去重

SELECT DISTINCT column_a, column_b, column_c FROM table_name;

6.求两张表的交集

SELECT * FROM table_aINTERSECTSELECT * FROM table_b;

7.按条件比较两个表，并选出不同行

SELECT column_a, column_b, column_cFROM table_aWHERE (column_a, column_b, column_c) NOT IN (    SELECT column_a, column_b, column_c     FROM table_b);

8.按照年月日时分秒进行分组统计，并按照总数排序

SELECT TO_CHAR(datetime, 'YYYY-MM-DD HH24:MI:SS') AS datetime_str, COUNT(*) AS count FROM table_name GROUP BY TO_CHAR(datetime, 'YYYY-MM-DD HH24:MI:SS') ORDER BY COUNT(*) DESC;

9.按照年-月进行分组统计，并按照总数排序

SELECT TO_CHAR(datetime, 'YYYY-MM') AS month_str, COUNT(*) AS count FROM table_name GROUP BY TO_CHAR(datetime, 'YYYY-MM') ORDER BY COUNT(*) DESC;

10.按照周进行分组统计，并按照总数排序

SELECT TRUNC(datetime, 'IW') AS week_date, COUNT(*) AS count FROM table_name GROUP BY TRUNC(datetime, 'IW') ORDER BY COUNT(*) DESC;

11.按照旬进行分组统计，并按照总数排序

SELECT TRUNC(datetime, 'MM') + MOD((TO_NUMBER(TO_CHAR(datetime, 'DD')) - 1), 30) / 10 AS decade, COUNT(*) AS count FROM table_name GROUP BY TRUNC(datetime, 'MM') + MOD((TO_NUMBER(TO_CHAR(datetime, 'DD')) - 1), 30) / 10 ORDER BY COUNT(*) DESC;

12.按照季度进行分组统计，并按照总数排序

SELECT TO_CHAR(datetime, 'Q') AS quarter, COUNT(*) AS count FROM table_name GROUP BY TO_CHAR(datetime, 'Q') ORDER BY COUNT(*) DESC;

13.按照年-周进行分组统计，并按照总数排序

SELECT TO_CHAR(datetime, 'IYYY-IW') AS year_week, COUNT(*) AS count FROM table_name GROUP BY TO_CHAR(datetime, 'IYYY-IW') ORDER BY COUNT(*) DESC;

14.按照月-旬进行分组统计，并按照总数排序

SELECT TRUNC(datetime, 'MM') || '-' || TRUNC(datetime, 'MM') + MOD((TO_NUMBER(TO_CHAR(datetime, 'DD')) - 1), 30) / 10 AS month_decade, COUNT(*) AS count FROM table_name GROUP BY TRUNC(datetime, 'MM') || '-' || TRUNC(datetime, 'MM') + MOD((TO_NUMBER(TO_CHAR(datetime, 'DD')) - 1), 30) / 10 ORDER BY COUNT(*) DESC;

15.按照年-季度进行分组统计，并按照总数排序

SELECT TO_CHAR(datetime, 'YYYY') || '-' || TO_CHAR(datetime, 'Q') AS year_quarter, COUNT(*) AS count FROM table_name GROUP BY TO_CHAR(datetime, 'YYYY') || '-' || TO_CHAR(datetime, 'Q') ORDER BY COUNT(*) DESC;

16.按照月进行分组统计，并按照单月最大值排序

SELECT TO_CHAR(datetime, 'YYYY-MM') AS month_str, MAX(column_name) AS max_value FROM table_name GROUP BY TO_CHAR(datetime, 'YYYY-MM') ORDER BY MAX(column_name) DESC;

17.求平均值

SELECT AVG(salary) FROM employees;

18.求总和

SELECT SUM(salary) FROM employees;

19.求最大值

SELECT MAX(salary) FROM employees;

20.求最小值

SELECT MIN(salary) FROM employees;

21.统计行数

SELECT COUNT(*) FROM employees;

22.分组统计

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

23.求百分比

SELECT salary, PERCENT_RANK() OVER (ORDER BY salary DESC) FROM employees;

24.多表联合统计

SELECT d.department_name, AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name;

整理这么多，整理不易，欢迎收藏，点赞，转发，关注我每天跟新一些运维知识。