窗口函數參考文章

SQL窗口函數

1. COALESCE 函數

COALESCE 函數的作用是從一組值中返回第一個非空（NULL）的值。如果所有的值都是 NULL，那么 COALESCE 函數會返回 NULL。

舉個例子，如果我們有一個表格包含了學生的姓名和昵稱，但有些學生沒有昵稱，我們可以使用 COALESCE 函數來選擇一個非空的名字作為顯示的名稱：

SELECT COALESCE(nickname, name) AS display_name
FROM students;

在這個例子中，如果 nickname 字段不為空，那么 display_name 將會使用昵稱；如果 nickname 字段為空，那么 display_name 將會使用姓名。

2. USING 函數

在 SQL 中，可以使用 USING 關鍵字來指定連接操作的列。當使用 JOIN 操作連接兩個表時，如果這兩個表中的連接列具有相同的名稱，可以使用 USING 關鍵字來簡化連接條件的書寫。
以下是使用 USING 關鍵字進行連接操作的示例：

SELECT *
FROM table1
JOIN table2 USING (common_column);

在上面的示例中，table1 和 table2 是要連接的兩個表，它們都具有一個名為 common_column 的列。使用 USING 關鍵字，可以省略連接條件的重復列名，系統會自動匹配這兩個表中具有相同名稱的列進行連接，不能指定多個列。

3. LEAD 函數

LEAD() 是一種窗口函數，用于獲取當前行之后的行的值。它可以幫助你在查詢中訪問結果集中下一個行的數據，而無需實際改變結果集的順序。

以下是 LEAD() 函數的基本語法：

LEAD(expression, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column)

• expression: 要獲取的值的列或表達式。
• offset: 指定要獲取的后續行的偏移量（默認為 1，表示下一行）。
• default: 可選參數，指定當沒有后續行時要返回的默認值（如果不提供，則默認為 NULL）。
• PARTITION BY partition_column: 可選子句，用于對結果集進行分區。
• ORDER BY order_column: 指定確定行之間順序的列。

通過使用 LEAD() 函數，你可以輕松地訪問結果集中當前行之后的行的數據，從而執行各種分析和計算操作。在之前的示例中，我展示了如何在查詢中使用 LEAD() 函數來獲取用戶的下一個刷題日期。

4. interval 函數

在SQL中，INTERVAL 5 MINUTE表示一個時間間隔，具體意思是“5分鐘”。這個用法通常用于在SQL語句中對時間進行加減操作，比如計算時間差、增加時間等。

例如，在MySQL中，你可以使用INTERVAL關鍵字來對時間進行加減操作。下面是一個示例：

SELECT NOW() AS current_time,NOW() + INTERVAL 5 MINUTE AS after_5_minutes;

這個查詢會返回當前時間以及加上5分鐘后的時間。INTERVAL 5 MINUTE表示增加5分鐘。

5. INSTR 函數

INSTR 函數是 SQL 中用來查找字符串中子串的函數。它的語法如下：

INSTR(string, substring)

其中，string 是要查找的字符串，substring 是要查找的子串。函數返回 substring 在 string 中第一次出現的位置，如果 substring 不在 string 中，則返回 0。

以下是一個示例：

SELECT INSTR('hello world', 'world');

這個查詢會返回 7，因為子串 'world' 在字符串 'hello world' 中第一次出現的位置是 7。

在你的例子中，INSTR(comment,'是')+INSTR(comment,'試')+INSTR(comment,'報名') > 0 的含義是，如果 comment 字符串中包含子串 '是'、'試' 或 '報名' 中的任意一個，那么該表達式的值就為真（即大于0）。

6. substring_index 函數

SUBSTRING_INDEX 函數用于在字符串中查找并返回某個分隔符之前或之后的子串。該函數的語法如下：

SUBSTRING_INDEX(str, delim, count)

• str 是要進行處理的字符串。
• delim 是用作分隔符的字符或子串。
• count 指定了要返回的子串在分隔符出現的次數。如果 count 為正數，函數將從左側開始查找；如果 count 為負數，函數將從右側開始查找。

例如，如果有字符串 'apple,banana,cherry'，想要獲取逗號后的第一個內容 'banana'，可以使用以下 SQL 查詢：

SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1) AS result;

這個查詢將返回 'banana'，因為它是逗號后的第一個內容。

7. LENGTH 函數和 CHAR_LENGTH 函數

在 SQL 中，LENGTH 函數和 CHAR_LENGTH 函數用于獲取字符串的長度，但它們之間有一些區別。

1. LENGTH 函數：

   • LENGTH 函數返回字符串中的字符數或字節數，取決于數據庫的字符集編碼。
   • 對于英文字符或單字節字符集，LENGTH 函數返回字符串中的字符數。
   • 對于多字節字符集（如 UTF-8），LENGTH 函數返回字符串中的字節數，而不是字符數。

2. CHAR_LENGTH 函數：

   • CHAR_LENGTH 函數返回字符串中的字符數，不受字符集編碼的影響。
   • 無論是單字節字符集還是多字節字符集，CHAR_LENGTH 函數都會返回字符串中的字符數。

例如，假設有一個包含中文字符的字符串 '你好，世界！'，如果使用 LENGTH 和 CHAR_LENGTH 函數來獲取其長度，結果可能如下：

SELECT LENGTH('你好，世界！') AS length_result, CHAR_LENGTH('你好，世界！') AS char_length_result;

在這個例子中，length_result 的值取決于數據庫的字符集編碼，而 char_length_result 的值將始終是 6，因為字符串中有 6 個字符。