OLAP 与 OLTP 场景下的典型查询

本文档给出 OLAP 与 OLTP 场景下的典型查询语句示例。由于多数场景的查询在实际应用过程中都具有融合性，因此本文并非严格分类。

注意！
了解一个场景的查询需求，是构建准确数据模型的关键之一。

1 OLAP

OLAP 即 Online Analytical Processing，联机分析处理场景，也常被称为数据仓库场景，多用于决策支持。它可以执行复杂的数据分析，以供数据科学家、业务分析员和知识工作者进行多角度观测，支持商业智能 (BI)、数据挖掘和其他决策支持应用。

1.1 排名计算

示例：

=# SELECT *, 
          row_number() OVER (ORDER BY c1 DESC),
          rank() OVER (ORDER BY c1 DESC),
          dense_rank() OVER (ORDER BY c1 DESC)
   FROM t1;

关键点：

使用窗口函数 row_number()、rank()、dense_rank() 计算不同类型的排名：
- row_number() 函数为结果集中的每一行分配一个唯一的连续排名值，按照 c1 列的降序进行排列。
- rank() 函数为结果集中的每个不同的 c1 值分配一个排名值，按照 c1 列的降序进行排列。如果有相同的 c1 值，则会跳过相应的排名。
- dense_rank() 函数为结果集中的每个不同的 c1 值分配一个排名值，按照 c1 列的降序进行排列。如果有相同的 c1 值，则会保留相应的排名，不会跳过。

1.2 首、末值

示例：

=# SELECT *,
          first_value(c1) OVER (ORDER BY c1 DESC),
          last_value(c1) OVER (ORDER BY c1 DESC)
   FROM t1;

1.3 窗口框架/滑动窗口

示例：

=# SELECT *,
          SUM(c1) OVER (ORDER BY c1 DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sum_c1 -- 添加框架声明，使得窗口涵盖从第一行到最后一行的所有行
   FROM t1;

使用窗口框架也可以控制聚集计算的范围，例如计算每个月的收入与前后各两个月的平均值：

=# SELECT month,
          revenue, 
          avg(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
   FROM t;

关键点：

使用窗口框架声明来界定窗口的边界。

1.4 分布统计

查询每个城市中，收入处于该市 40%-70% 之间（含）水平的人口记录。

=# SELECT id,name,salary,city
   FROM (
    SELECT *,
           ntile(10) OVER (
            PARTITION BY city
            ORDER BY salary DESC
           ) AS bucket
    FROM city_salary
   ) AS t
   WHERE t.bucket >= 4 AND t.bucket <= 7;

关键点：

该查询使用窗口函数 ntile() 来对 city_salary 表中的记录进行分桶，并从 10 个桶中筛选出第 4 到第 7 个分桶的记录。

1.5 偏移计算

计算每个月收入相对上个月变化的比例：

=# WITH detail(m, r, l) AS (
    SELECT month, 
           revenue,
           lag(revenue, 1)  OVER (ORDER BY month) AS last_month_revenue
    FROM sales
   )
   SELECT m, ((r-l)::float8/l * 100)::decimal(18,2) || '%'
   FROM detail;

关键点：

使用公共表表达式（Common Table Expression, CTE）来计算每个月份的销售收入以及与上个月相比的增长百分比。
(r-l)::float8/l * 100)::decimal(18,2) || '%'：此表达式用于增长百分比的计算。其将当前月份的销售收入减去上个月的销售收入，并将结果除以上个月的销售收入。最后使用类型转换将结果转换为百分比格式，并将小数点后两位的精度保留。

1.6 对窗口进行聚集查询

查询每个月收入占当季度收入的比例：

=# SELECT *,
          revenue::real / quarter_revenue * 100 AS percent_of_quarter
   FROM (
   SELECT quarter,
          month,
          revenue,
          sum(revenue) OVER (PARTITION BY quarter) AS quarter_revenue
   FROM sales2
   ) AS t;

1.7 变化点

变化点列出相比于前一条有变化的记录，该类型查询适合在比较平稳的数据集中找发生变化的点：

=# SELECT time, read FROM (
        SELECT time,
            read,
            read - lag(read) OVER (ORDER BY TIME) AS diff
        FROM disk
        WHERE time BETWEEN '2021-04-10 21:00:00'::timestamp AND '2021-04-10 21:01:00'::timestamp
        AND tag_id = 1 ) ht
    WHERE diff IS NULL OR diff != 0
    ORDER BY time;

关键点：

使用窗口函数 lag() 计算每个时间点读取值与前一个时间点读取值之间的差异。
过滤条件 WHERE diff IS NULL OR diff != 0 意味着只筛选出差异值为 NULL 或非零的时间点记录。

1.8 连续百分率

计算磁盘阵列在按照 tag_id 排序的情况下，读速度处于 20% 位置，写速度处于 30% 位置的值：

=# SELECT tag_id,
    percentile_cont(0.2) WITHIN GROUP (ORDER BY read) AS read,
    percentile_cont(0.3) WITHIN GROUP (ORDER BY write) AS write
    FROM disk
    GROUP BY tag_id
    ORDER BY tag_id;

如果函数参数值为 0.5，则相当于计算中位数。

1.9 指标对比分析

例如使用多表关联聚集对某零售店的指标进行节假日对比计算：

=# WITH holiday_rst AS (
    SELECT year,holiday,date,row_number() OVER(PARTITION BY year,holiday ORDER BY date)
    AS the_day_of_the_holiday
    FROM dim_holiday_info
    )
   SELECT t.year,t.holiday,t.date,t.the_day_of_the_holiday,b.date AS corresponding_date_of_previous_year
   FROM holiday_rst t
   LEFT JOIN holiday rst b
   ON t.year = b.year + 1
   AND t.holiday = b.holiday
   AND t.the_day_of_the_holiday = b.the_day_of_the_holiday;

关键点：

该查询使用公共表表达式来计算每个假期的日期，并为每个假期在每年中的日期进行编号。
查询结果将包含当前年份的假期信息，以及对应的前一年同一假期的日期。

1.10 多维数据分析

在商业智能（Business Intelligence, BI）领域，需要从不同维度透视一个数据集，并通过数据可视化手段，将数据蕴藏的知识呈现给决策者，实现其商业价值。

数据可视化是多维数据分析的重要环节，通过特定的 BI 分析软件，将数据库输出的数据立方体数组描绘成图形、图像及计量表，使得大数据内在的统计特征可以被决策者识别和感知，还可以进行交互和预测。

1.10.1 多维度聚集计算

从多个维度进行聚集计算，并将结果一同展示：

=# SELECT city,
          season, 
          category, 
          sum(value) 
   FROM bi_sales
   GROUP BY GROUPING SETS (city, season, category)
   ORDER by (city);
   city   | season | category  | sum
----------+--------+-----------+------
 Beijing  |        |           | 1098
 Shanghai |        |           | 1089
 Guangzhou|        |           | 1080
          | Q3     |           | 1989
          |        | Groceries | 1089
          | Q1     |           |  189
          |        | Electrics |  999
          |        | Books     | 1179
          | Q2     |           | 1089
(9 rows)

可以看到，求和聚集结果中分别展示了：

城市维度的计算：city 列中每个城市分别的 value 值总和；
季度维度的计算：season 列中每个季度分别的 value 值总和；
类目维度的计算：category 列中每种产品类目分别的 value 值总和。

2 OLTP

OLTP 即 Online Transaction Processing，联机事务处理场景，多用于日常事务处理。它能够快速、准确地执行 ATM、网上银行、收银机、电子商务和实体店购买，以及旅馆和机票预订等的数据处理，实时执行许多数据库事务（变更、插入、删除或查询操作）。

2.1 即席查询

即席查询（Ad Hoc）是用户根据自己的需求，灵活的选择查询条件，系统能够根据用户的选择快速生成相应的统计报表。即席查询与普通应用查询最大的不同是普通的应用查询是定制开发的，而即席查询是由用户自定义查询条件的。

=# SELECT * FROM sales WHERE customer_id=100;