SQL 数据分析 · 指南 · 2026年10月30日 · 13 分钟阅读

SQL 速查表（数据分析师版）：常用语句和函数，每条都附可运行的例子

这一页汇总了数据分析师每天要用的 SQL：查询的各个子句、筛选条件、聚合函数、JOIN、CASE、NULL 处理、子查询和 CTE、窗口函数，以及最常用的日期和字符串函数。每一节都有一张简短的速查表和一个附带真实输出的例子，让你看到语法实际返回什么，而不是靠猜。

所有例子都在 Nomto SQL 课程的航空公司数据库上运行：104 个机场、9 种机型、2026 年 7 月 12 日至 9 月 10 日的 33,121 个航班、262,788 笔订单，金额单位为美元，时间为莫斯科时间。语法是 PostgreSQL。任何一个查询都可以原样粘贴到 Nomto 的 SQL 练习场里运行。

下文用到的表

查询骨架：各子句及其执行顺序

你按表格第一列的顺序书写，数据库却按最后一列的顺序执行。这就是为什么 SELECT 里定义的别名能在 ORDER BY 里用，却不能在 WHERE 里用。

SELECT model, range
FROM aircrafts
WHERE range >= 5000
ORDER BY range DESC
LIMIT 3;

筛选（WHERE）

SELECT COUNT(*) FILTER (WHERE status IN ('Departed', 'Arrived')) AS flown,
       COUNT(*) FILTER (WHERE scheduled_departure BETWEEN '2026-08-01' AND '2026-08-07 23:59:59') AS first_week_aug,
       COUNT(*) FILTER (WHERE actual_departure IS NULL) AS not_departed
FROM flights;

FILTER (WHERE ...) 让你在一个查询里放三个不同的筛选条件，每个 COUNT 只看自己的那部分行。日期范围用 >= '2026-08-01' AND < '2026-08-08' 比 BETWEEN 更稳妥，因为你不用记得写 23:59:59。

聚合函数

SELECT COUNT(*)                                  AS bookings,
       COUNT(DISTINCT DATE_TRUNC('day', book_date)) AS days,
       SUM(total_amount)                         AS revenue,
       ROUND(AVG(total_amount), 2)               AS avg_booking,
       MIN(total_amount)                         AS min_b,
       MAX(total_amount)                         AS max_b
FROM bookings;

GROUP BY 和 HAVING

SELECT 里的每一列都必须出现在 GROUP BY 中，或者放在聚合函数里。WHERE 在分组前筛选行；HAVING 在分组后筛选组。

SELECT city, COUNT(*) AS airports
FROM airports
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY airports DESC, city;

两者的区别详见 GROUP BY 和 HAVING 的区别。

JOIN

从另一张表查一个值：

SELECT f.flight_no, a.model
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE f.flight_id = 1;

找出没有对应记录的行，这里是从不执飞的机型：

SELECT a.model
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.flight_id IS NULL;

要记住的陷阱：一行匹配到多行时，它会按匹配次数重复。把 bookings 和 tickets JOIN 之后再对 bookings.total_amount 求和，得到的是 343,206,192，而不是正确的 207,669,809，因为有多张机票的订单被重复计算了。SQL JOIN 用法详解 详细讲了这种行数膨胀。

CASE：条件判断

CASE WHEN condition1 THEN value1
     WHEN condition2 THEN value2
     ELSE default
END

条件从上到下依次检查，第一个为真的条件生效，所以把范围最窄的放在最前面。

SELECT CASE WHEN total_amount >= 1000 THEN 'large'
            WHEN total_amount >= 300  THEN 'medium'
            ELSE 'small'
       END AS size,
       COUNT(*) AS bookings
FROM bookings
GROUP BY size
ORDER BY bookings DESC;

SUM(CASE WHEN ... THEN 1 ELSE 0 END) 是按条件计数的通用写法；在 PostgreSQL 里 COUNT(*) FILTER (WHERE ...) 效果相同。

NULL 处理

SELECT flight_no, scheduled_departure, status,
       COALESCE(CAST(actual_departure AS VARCHAR), 'not departed') AS actual
FROM flights
WHERE flight_no = 'PG0405'
  AND scheduled_departure BETWEEN '2026-08-10' AND '2026-08-13'
ORDER BY scheduled_departure;

一个安全的百分比写法，表为空时返回 NULL 而不是报错：

SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'Cancelled') / NULLIF(COUNT(*), 0), 2) AS cancelled_pct
FROM flights;

要写 100.0，不要写 100：两个整数相除时，有些数据库会做整数除法，结果是 0。

子查询和 CTE

子查询就是嵌在另一个查询里的查询。高于平均金额的订单数：

SELECT COUNT(*) AS above_avg
FROM bookings
WHERE total_amount > (SELECT AVG(total_amount) FROM bookings);

CTE（WITH）给子查询起一个名字，让查询可以从上往下读。先算每天的航班数，再算这些日航班数的最小值、平均值和最大值：

WITH daily AS (
  SELECT CAST(scheduled_departure AS DATE) AS day, COUNT(*) AS flights
  FROM flights
  GROUP BY day
)
SELECT MIN(flights) AS min_day, ROUND(AVG(flights), 1) AS avg_day, MAX(flights) AS max_day
FROM daily;

需要聚合两次（日计数的平均值、占总数的比例），或者查询超过一屏时，就用 CTE。

窗口函数

窗口函数为每一行加上一个值，但不合并行。结构是 function() OVER (PARTITION BY ... ORDER BY ... frame)。

一个查询里同时做环比（和前一天比较）和累计求和：

SELECT CAST(book_date AS DATE) AS day,
       COUNT(*) AS bookings,
       LAG(COUNT(*)) OVER (ORDER BY CAST(book_date AS DATE)) AS prev_day,
       SUM(COUNT(*)) OVER (ORDER BY CAST(book_date AS DATE)) AS running_total
FROM bookings
GROUP BY day
ORDER BY day
LIMIT 4;

用 ROW_NUMBER 取每组第一名：每种机型起飞最多的机场。

SELECT *
FROM (
  SELECT a.model, f.departure_airport, COUNT(*) AS flights,
         ROW_NUMBER() OVER (PARTITION BY a.model ORDER BY COUNT(*) DESC) AS rn
  FROM flights f
  JOIN aircrafts a USING (aircraft_code)
  GROUP BY a.model, f.departure_airport
) t
WHERE rn = 1
ORDER BY flights DESC
LIMIT 4;

不能在同一个查询的 WHERE 里筛选窗口函数的结果，所以要套一层子查询。完整讲解见 SQL 窗口函数详解 和 累计求和。

日期和时间

每月航班数：

SELECT DATE_TRUNC('month', scheduled_departure) AS month, COUNT(*) AS flights
FROM flights
GROUP BY month
ORDER BY month;

这里的 7 月和 9 月都不是完整月份（时刻表从 7 月 12 日到 9 月 10 日），比较月份时值得明确说出来。

日期加减：

SELECT book_ref, book_date,
       book_date + INTERVAL '30 days' AS plus_30,
       '2026-08-11'::DATE - CAST(book_date AS DATE) AS days_ago
FROM bookings
ORDER BY book_ref
LIMIT 2;

起飞延误最长的航班，以分钟计：

SELECT flight_no, scheduled_departure, actual_departure,
       ROUND(EXTRACT(EPOCH FROM actual_departure - scheduled_departure) / 60) AS delay_min
FROM flights
WHERE status = 'Arrived'
ORDER BY delay_min DESC
LIMIT 3;

完全没有数据的日子（某天零订单，在 GROUP BY 结果里根本不会出现）怎么处理，见 date spine 指南。

字符串

乘客姓名以「名 姓」的形式全大写存储，联系方式是 JSON：

SELECT passenger_name,
       LOWER(SPLIT_PART(passenger_name, ' ', 2)) AS last_lower,
       contact_data ->> 'phone' AS phone
FROM tickets
ORDER BY ticket_no
LIMIT 3;

拼一个标签，再在某个字符处截断字符串：

SELECT airport_name || ' (' || airport_code || ')' AS label,
       SUBSTRING(timezone, 1, POSITION('/' IN timezone) - 1) AS region
FROM airports
WHERE city = 'Moscow'
ORDER BY airport_code;

更多函数见 SQL 字符串函数。

这张速查表帮你避开的错误

• = NULL。 永远写 IS NULL；等号不会返回任何行，也不会报错。
• LIMIT 不配 ORDER BY。 你拿到的是某几行，而不是排在最前面的几行。
• 一对多 JOIN 之后求和。 先把「多」的那一边聚合好，再 JOIN。
• 整数除法。 100 * a / b 可能被舍成 0；写 100.0。
• 在 WHERE 里筛选窗口函数。 套一层子查询或 CTE。
• 对时间戳用 BETWEEN。 '2026-08-07' 表示当天零点；用 >= 起始日和 < 次日。

方言差异

这里的一切都是 PostgreSQL 语法。大部分在其他数据库里也一样；常见的差异如下：

怎么用这张速查表

工作时把它开着，但别试图背下来。语法是在解决你关心的问题时用熟的。在 Nomto 的 SQL 练习场里，上面每个查询都跑在同一个航空公司数据库上，改一个筛选条件或分组列，看看结果怎么变。如果你刚入门，SQL 基础查询语句 一步步讲解了本页的前半部分，SQL 练习题 则提供了练习后半部分的题目。

常见问题

数据分析师需要掌握哪些 SQL？

要熟练掌握：SELECT、WHERE、GROUP BY、HAVING、JOIN、CASE、NULL 处理、子查询和 CTE，以及日期函数。然后是窗口函数（ROW_NUMBER、LAG、累计求和），大多数数据分析师面试都会考。索引、存储过程和数据库管理对这个岗位来说就没那么重要了。

这张速查表适用于 MySQL 和 SQL Server 吗？

子句、JOIN、聚合函数、CASE、COALESCE、子查询、CTE 和窗口函数的用法都一样。日期函数、字符串拼接和 LIMIT 有差异；上面的方言对照表列出了常见情况。

子查询和 CTE 有什么区别？

两者作用相同。CTE 给子查询起了个名字并把它挪到最前面，让长查询可以分步阅读。哪个好读就用哪个；只要有两步或以上，通常是 CTE 更好读。

不安装数据库能运行这些例子吗？

能。打开 Nomto 的 SQL 练习场，粘贴查询，就能在浏览器里对同一份航空公司数据运行。

其他语言： English, Español, Português (Brasil), Tiếng Việt