SQL server数据分析常用语句

在SQL Server中，常用的数据分析语句包括以下几类，涵盖了聚合、排序、分组、过滤等操作：

1. 基本查询

• SELECT：从表中查询数据

  SELECT column1, column2 FROM table_name;
  

2. 聚合函数

• COUNT：计数

  SELECT COUNT(*) FROM table_name;
  

• SUM：求和

  SELECT SUM(column_name) FROM table_name;
  

• AVG：平均值

  SELECT AVG(column_name) FROM table_name;
  

• MIN：最小值

  SELECT MIN(column_name) FROM table_name;
  

• MAX：最大值

  SELECT MAX(column_name) FROM table_name;
  

3. GROUP BY：分组

• 分组查询：用于按某列分组进行聚合分析

  SELECT column_name, COUNT(*) FROM table_name
  GROUP BY column_name;
  

4. HAVING：过滤分组数据

• 条件过滤：对分组后的数据进行过滤

  SELECT column_name, COUNT(*) FROM table_name
  GROUP BY column_name
  HAVING COUNT(*) > 5;
  

5. ORDER BY：排序

• 按升序或降序排序：

  SELECT column_name FROM table_name
  ORDER BY column_name ASC;  -- 升序
  SELECT column_name FROM table_name
  ORDER BY column_name DESC; -- 降序
  

6. JOIN 操作：连接多个表

• INNER JOIN：返回两个表中匹配的行

  SELECT a.column1, b.column2
  FROM table_a a
  INNER JOIN table_b b ON a.id = b.id;
  

• LEFT JOIN：返回左表的所有行和右表的匹配行

  SELECT a.column1, b.column2
  FROM table_a a
  LEFT JOIN table_b b ON a.id = b.id;
  

• RIGHT JOIN：返回右表的所有行和左表的匹配行

  SELECT a.column1, b.column2
  FROM table_a a
  RIGHT JOIN table_b b ON a.id = b.id;
  

• FULL JOIN：返回两个表中所有行

  SELECT a.column1, b.column2
  FROM table_a a
  FULL JOIN table_b b ON a.id = b.id;
  

7. 子查询：嵌套查询

• 子查询（返回单值）：

  SELECT column1
  FROM table_name
  WHERE column2 = (SELECT column2 FROM another_table WHERE condition);
  

8. 窗口函数：分区分析

• ROW_NUMBER()：为结果集中的每一行分配唯一的行号

  SELECT column1, ROW_NUMBER() OVER (ORDER BY column2) AS row_num
  FROM table_name;
  

• RANK()：为结果集中的每一行分配排名

  SELECT column1, RANK() OVER (ORDER BY column2) AS rank_num
  FROM table_name;
  

• NTILE()：将数据分成n个组

  SELECT column1, NTILE(4) OVER (ORDER BY column2) AS quartile
  FROM table_name;
  

9. CASE WHEN：条件表达式

• CASE：在查询中实现条件逻辑

  SELECT column1,
         CASE
             WHEN column2 > 100 THEN 'High'
             WHEN column2 > 50 THEN 'Medium'
             ELSE 'Low'
         END AS category
  FROM table_name;
  

10. TOP：限制返回的行数

• 返回前n行数据：

  SELECT TOP 5 column1, column2 FROM table_name
  ORDER BY column1 DESC;
  

11. DISTINCT：去重

• 去除重复的行：

  SELECT DISTINCT column1 FROM table_name;
  

12. 日期函数：处理日期类型数据

• GETDATE()：当前日期和时间

  SELECT GETDATE();
  

• YEAR()、MONTH()、DAY()：提取日期中的年、月、日

  SELECT YEAR(column_date) AS year, MONTH(column_date) AS month
  FROM table_name;
  

13. TOP N 按照时间统计

• 获取按时间分组的前N条记录：

  SELECT TOP 3 column1, column2
  FROM table_name
  WHERE column_date BETWEEN '2024-01-01' AND '2024-12-31'
  ORDER BY column2 DESC;
  

14. CTE（公用表表达式）：临时结果集

• 使用CTE简化复杂查询：

  WITH CTE AS (
      SELECT column1, COUNT(*) AS count
      FROM table_name
      GROUP BY column1
  )
  SELECT * FROM CTE;
  

15. 递归查询：使用CTE进行递归

• 示例：递归查询父子关系：

  WITH RecursiveCTE AS (
      SELECT id, parent_id, name
      FROM table_name
      WHERE parent_id IS NULL
      UNION ALL
      SELECT t.id, t.parent_id, t.name
      FROM table_name t
      INNER JOIN RecursiveCTE r ON t.parent_id = r.id
  )
  SELECT * FROM RecursiveCTE;