高级数据分析模式

Databricks SQL 入门

Kevin Barlow

Data Manager

动机

湖仓图——分析

子查询

通过"嵌套"SQL查询实现
- 将嵌套查询视为一张新"表"
- 与创建视图效果相似
适用于多种场景
- 复用另一查询的结果
- 简化复杂查询
- 在查询内做聚合

SELECT store, totalRev, product

FROM (
    SELECT count(*) as count,
          sum(revenue) as totalRev,
          min(price) as minPrice,
          max(units) as maxUnits,
          region,
          store,
          product
      FROM sales 
      GROUP BY region, store, product
) q

窗口函数

一类 SQL 函数与技术
在特定行范围内计算
- 这些行彼此有关联
示例：
- 基于时间范围计算指标
- 计算相邻行的变化
- 跨多维度计算

SELECT name,
       dept,
       RANK() OVER 
           (PARTITION BY dept 
         ORDER BY salary) AS rank
  FROM employees;

name	dept	salary	rank
Lisa	Sales	10000	1
Alex	Sales	30000	2
Fred	Engineering	21000	1
Tom	Engineering	23000	2

高级 Databricks SQL 函数

RANK()

在给定分区内比较行并为每行计算名次

SELECT a,
       b,
       RANK() OVER(PARTITION BY a ORDER BY b DESC),
    FROM table_name;

a	b	rank
A1	3	1
A1	1	3
A1	2	2
A2	1	1

LAG() 与 LEAD()

返回上一行（LAG()）或下一行（LEAD()）的值

SELECT a,
       b,
       LAG(b) OVER (PARTITION BY a ORDER BY b)
    FROM table_name;

a	b	lag
A1	3	NULL
A1	1	3
A1	2	1
A2	1	NULL

¡Vamos a practicar!

Databricks SQL 入门