用 SQL 找出某只股票连续上涨的最长天数

解法

涉及多张中间表:

SELECT MAX(consecutive_day)
FROM (SELECT COUNT(*) as consecutive_day
  FROM (SELECT trade_date, SUM(rise_mark) OVER (ORDER BY trade_date) AS days_no_gain
     FROM (SELECT trade_date,
                CASE
                    WHEN closing_price > LAG(closing_price) OVER (ORDER BY trade_date)
                         THEN 0
                    ELSE 1 END AS rise_mark
           FROM stack_price) subquery1) subquery2
  GROUP BY days_no_gain) subquery3;
1
2
3
4
5
6
7
8
9
10

(完)

---

补充

Over 语法

SELECT
  product_id,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS total_sales,
  SUM(sale_amount) OVER (PARTITION BY product_id) AS running_total
FROM
  sales;
1
2
3
4
5
6
7
8

basic:

order_id | customer_id | order_amount
-------------------------------------
1        | 1           | 100
2        | 1           | 150
3        | 2           | 200
4        | 2           | 50
5        | 2           | 120
1
2
3
4
5
6
7

result:

order_id | customer_id | order_amount | total_amount | running_total
-------------------------------------------------------------------
1        | 1           | 100          | 100          | 250
2        | 1           | 150          | 250          | 250
3        | 2           | 200          | 200          | 370
4        | 2           | 50           | 250          | 370
5        | 2           | 120          | 370          | 370
1
2
3
4
5
6
7

Window function

A window function is a type of function in SQL that performs calculations across a set of rows called a “window.” The window is defined by the OVER clause, which specifies the partitioning and ordering of the rows.

SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_id)
1

1. SUM(order_amount): This is the window function itself, in this case, the SUM function is used to calculate the sum of the order_amount.
2. OVER: It introduces the window function and specifies the window’s characteristics.
3. PARTITION BY customer_id: This clause divides the rows into separate partitions based on the customer_id. Each partition will have its own calculation of the sum.
4. ORDER BY order_id: This clause determines the order in which the rows are processed within each partition. In this case, it orders the rows by the order_id.

SUM(order_amount) OVER (PARTITION BY customer_id)
1

Without the ORDER BY clause, the entire partition is considered, and the calculation is performed on all rows with the same customer_id.

The window function, in combination with the OVER clause, allows us to perform calculations within specific partitions and orderings defined by the columns specified. It provides a way to aggregate or calculate values based on a subset of rows without collapsing the result set or using subqueries.

Other common window functions include ROW_NUMBER(), AVG(), MIN(), MAX(), and LEAD()/LAG(), among others. Each function has its own specific purpose and behavior within the window frame defined by the OVER clause.

OLAP / OLTP

SQL 作为查询语言而发明, 名字叫 “结构化查询”（structured query）, 数学基础是 “关系模型”, 没有考虑复杂计算 (与之相对的是离散数学, 把 “数据存储 + 数据计算” 做在一起)

由于数据处理和计算的需求越来越大, 于是 OLAP（联机分析处理）和 OLTP（联机事务处理）的概念就诞生了.

• OLAP: Online Analytical Processing.
• OLTP: Online Transaction Processing.

它们都基于数据库, 属于"数据库 + 计算层". 所以受限于数据库, 在处理海量数据时, 有效率瓶颈.