KWAN的解忧杂货铺

# 一.LAG 和 LEAD

# 1.LAG 和 LEAD 概述

在数据库查询中，开窗函数（Window Functions）是一种强大的工具，用于在结果集的子集上执行计算。LAG和LEAD是两个常用的开窗函数，它们允许你在当前行的前面或后面的行中访问数据。 在数据库中，LAG和LEAD函数的全称是：

  • LAG函数的全称是"LAST VALUE"。
  • LEAD函数的全称是"LEAD VALUE"。

这两个函数是 SQL 标准中的开窗函数，它们允许在查询结果的子集上执行类似于聚合函数的计算，但不会导致结果集的单一行。它们的目的是在当前行的前面（LAG）或后面（LEAD）的行中访问数据，使得在处理时间序列数据或需要比较相邻行之间的值时非常有用。

# 2.作用特性

Lag 和 Lead 分析函数可以在同一次查询中取出同一字段的前 N 行的数据(Lag)和后 N 行的数据(Lead)作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag 和 Lead 函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是 LAG 和 LEAD 与 left join、right join 等自连接相比，效率更高，SQL 更简洁。

# 3.LAG 函数

LAG函数用于获取结果集中当前行之前的某一行的值。语法如下：

LAG (expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
1
  • expression: 要检索的列或表达式。
  • offset: 要返回的行数，如果不指定，默认为 1，即上一行。
  • default: 当指定的行数超出结果集范围时，返回的默认值。

# 4.LEAD 函数

LEAD函数用于获取结果集中当前行之后的某一行的值。语法如下：

LEAD (expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
1
  • expression: 要检索的列或表达式。
  • offset: 要返回的行数，如果不指定，默认为 1，即下一行。
  • default: 当指定的行数超出结果集范围时，返回的默认值。

# 5.LAG 示例

考虑一个名为sales的表，包含销售数据：

CREATE TABLE sales (
    sale_date DATE,
    revenue INT
);

INSERT INTO sales VALUES
('2023-01-01', 100),
('2023-01-02', 150),
('2023-01-03', 200),
('2023-01-04', 120);
1
2
3
4
5
6
7
8
9
10

使用LAG函数，你可以获取前一天的销售额：

SELECT sale_date                                             -- 日期
     , revenue                                               -- 当前销售额
     , LAG(revenue) OVER (ORDER BY sale_date) AS lag_revenue -- 前一行的销售额
FROM sales;
1
2
3
4

结果：

| sale_date  | revenue | lag_revenue |
|------------|---------|-------------|
| 2023-01-01 | 100     | NULL        |
| 2023-01-02 | 150     | 100         |
| 2023-01-03 | 200     | 150         |
| 2023-01-04 | 120     | 200         |
1
2
3
4
5
6

# 6.LEAD 示例

使用LEAD函数，你可以获取后一天的销售额：

SELECT sale_date                                               -- 日期
     , revenue                                                 -- 日期
     , LEAD(revenue) OVER (ORDER BY sale_date) AS lead_revenue -- 后一行的销售额
FROM sales;
1
2
3
4

结果：

| sale_date  | revenue | lead_revenue |
|------------|---------|--------------|
| 2023-01-01 | 100     | 150          |
| 2023-01-02 | 150     | 200          |
| 2023-01-03 | 200     | 120          |
| 2023-01-04 | 120     | NULL         |
1
2
3
4
5
6

在这两个示例中，LAG和LEAD函数通过ORDER BY子句按销售日期对结果集进行排序。这允许你在时间序列数据中访问前一行或后一行的值，以进行比较或计算差异等操作。

# 7.总结说明

  1. 时间序列分析： 在时间序列数据中，你可能需要比较当前时间点的值与前一时间点或后一时间点的值，以便分析趋势、变化或周期性。
  2. 差异计算： 你可能需要计算当前行与前一行或后一行的差异，例如，计算每日销售额的增长或减少量。
  3. 移动平均计算： 通过结合LAG和LEAD函数，你可以计算移动平均值，以平滑数据并更好地理解趋势。
  4. 排名和百分比计算： 通过比较当前行与前一行或后一行的值，你可以进行排名或计算百分比变化，从而了解相对于其他行的位置或变化。
  5. 查找极值点： 通过比较当前值与相邻值，你可以轻松地识别极值点，例如找到峰值或谷值。
  6. 数据填充： 你可以使用LAG和LEAD函数来填充缺失的数据。如果某些行的数据缺失，你可以使用相邻行的值进行填充。
  7. 数据平滑计算： 你可以使用LAG和LEAD函数来进行数据平滑计划,让结果绘制的曲线更加平滑。这种操作可以代替表的自联接，并且 LAG 和 LEAD 有更高的效率。

LAG 和 LEAD 主要用来计算当前行的前后 N 行的这种场景，一般情况下我们会对数据进行排序，因为只有在有序的情况下，前面多少行和后面多少行才有意义。

LAG 和 LEAD 可以用在某些场景下代替自关联的写法。

# 二.JSON_CONTAINS

# 1.什么是 JSON_CONTAINS?

在 MySQL 中，JSON_CONTAINS() 是一个用于检查 JSON 数据中是否包含指定值或路径的函数。它用于执行 JSON 数据类型的查询和比较操作。

# 2.基础语法

JSON_CONTAINS() 函数的语法如下：

JSON_CONTAINS(json_doc, value[, path])
1

其中：

  • json_doc 是要搜索的 JSON 文档。
  • value 是要查找的值。
  • path 是可选参数，用于指定要搜索的路径。如果省略该参数，则在整个 JSON 文档中进行搜索。

函数的返回值是一个布尔值，表示是否找到了匹配的值或路径。

# 3.使用示例

sql = f"select count(*) from indicator_library where JSON_CONTAINS(synonyms,'\"{indicatorName}\"')"
count = session.execute(text(sql)).scalar()
1
2
3

以下是一些示例用法：

  1. 在整个 JSON 文档中搜索指定的值：
SELECT JSON_CONTAINS('{"name": "John", "age": 30}', 'John');
1

这将返回结果为 1，表示 JSON 文档中包含值为 'John' 的元素。

  1. 在指定路径下搜索指定的值：
SELECT JSON_CONTAINS('{"person": {"name": "John", "age": 30}}', 'John', '$.person.name');
1

这将返回结果为 1，表示在路径 $.person.name 下找到了值为 'John' 的元素。

  1. 使用列和参数进行查询：
SELECT * FROM my_table WHERE JSON_CONTAINS(json_data, 'value', '$.path');
1

这将在名为 my_table 的表中的 json_data 列中搜索具有指定值和路径的 JSON 数据。

请注意，JSON_CONTAINS() 函数还可以与其他函数和操作符一起使用，以进行更复杂的 JSON 查询操作。在执行 JSON 查询之前，请确保使用的 MySQL 版本支持 JSON 数据类型和相关函数。

# 三.JSON_TABLE

# 1.什么是 JSON_TABLE?

在 MySQL 中，JSON_TABLE() 函数用于将 JSON 数据解析为关系型表格形式。它允许您从 JSON 对象中提取数据并将其作为表的行进行查询和操作。

# 2.基本语法

JSON_TABLE() 函数的语法如下：

JSON_TABLE(json_doc, path COLUMNS (column_list))
1

其中：

  • json_doc 是要解析的 JSON 文档。
  • path 是一个字符串，用于指定要提取数据的路径。
  • COLUMNS (column_list) 是定义生成的表格的列的部分。

列定义部分包含列名和数据类型，您可以根据需要定义多个列。每个列定义由列名和数据类型组成，可以使用 AS 关键字为列指定别名。

# 3.使用示例

SELECT d.*
      FROM dimension d,
      JSON_TABLE(
          d.enumeration_values,
          "$[*]" COLUMNS (
              name VARCHAR(255) PATH "$.name",
              sql_mapping INT PATH "$.sql_mapping",
              equivalent_word JSON PATH "$.equivalent_word"
          )
      ) AS jt
      WHERE 1 = 1
   and JSON_CONTAINS(jt.equivalent_word, '\"包\"') ;
1
2
3
4
5
6
7
8
9
10
11
12

以下是一个示例用法：

SELECT *
FROM JSON_TABLE('{"employees": [{"name": "John", "age": 30}, {"name": "Jane", "age": 35}]',
               '$.employees[*]'
               COLUMNS (
                   name VARCHAR(50) PATH '$.name',
                   age INT PATH '$.age'
               )
             ) AS jt;
1
2
3
4
5
6
7
8

在上面的示例中，我们从 JSON 对象中提取名为 "employees" 的数组，并将其解析为表格形式。表格中的每一行代表一个员工，具有名字和年龄两列。

PATH 子句用于指定数据在 JSON 文档中的路径。'$' 表示整个 JSON 文档，'$.employees[*]' 表示 employees 数组中的所有元素。

在查询中，我们使用 JSON_TABLE() 函数将 JSON 数据解析为表格，并将其作为子查询（使用 AS 关键字指定别名 jt）。

最后，我们通过选择所有列（SELECT *）从生成的表格中检索数据。

请注意，JSON_TABLE() 函数在 MySQL 8.0 版本及以上可用。

上次更新: 11/2/2024, 2:20:26 PM

←