MySql窗口函數(shù)

MySQL從8.0開始支持窗口函數(shù)。也就是分析函數(shù)

創(chuàng)新互聯(lián)基于成都重慶香港及美國等地區(qū)分布式IDC機房數(shù)據(jù)中心構(gòu)建的電信大帶寬，聯(lián)通大帶寬，移動大帶寬，多線BGP大帶寬租用,是為眾多客戶提供專業(yè)多線服務(wù)器托管報價，主機托管價格性價比高，為金融證券行業(yè)服務(wù)器托管，ai人工智能服務(wù)器托管提供bgp線路100M獨享，G口帶寬及機柜租用的專業(yè)成都idc公司。

序號函數(shù)：ROW_NUMBER()、RANK()、DENSE_RANK()

分布函數(shù)：PERCENT_RANK()、CUME_DIST()

前后函數(shù)：LAG()、LEAD()

頭尾函數(shù)：FIRST_VALUE()、LAST_VALUE()

其它函數(shù)：NTH_VALUE()、NTILE()

例子：

首先有一個表字段：id score（分數(shù)）user_id

1.序號函數(shù)：ROW_NUMBER()、RANK()、DENSE_RANK()

用途：顯示分區(qū)中的當(dāng)前行號,對查詢結(jié)果進行排序.

ROW_NUMBER()：順序排序——1、2、3 RANK()：并列排序，跳過重復(fù)序號——1、1、3 DENSE_RANK()：并列排序，不跳過重復(fù)序號——1、1、2

執(zhí)行sql:

2.分布函數(shù)：PERCENT_RANK()、CUME_DIST()

用途：每行按照公式(rank-1) / (rows-1)進行計算。其中，rank為RANK()函數(shù)產(chǎn)生的序號，rows為當(dāng)前窗口的記錄總行數(shù)

3.前后函數(shù)：LAG()、LEAD()

LAG和LEAD分析函數(shù)可以在同一次查詢中取出同一字段的前N行的數(shù)據(jù)(LAG)和后N行的數(shù)據(jù)(LEAD)作為獨立的列

在實際應(yīng)用當(dāng)中，若要用到取今天和昨天的某字段差值時，LAG和LEAD函數(shù)的應(yīng)用就顯得尤為重要。當(dāng)然，這種操作可以用表的自連接實現(xiàn)，但是LAG和LEAD與LEFT JOIN、RIGHT JOIN等自連接相比，效率更高，SQL更簡潔。下面我就對這兩個函數(shù)做一個簡單的介紹。

函數(shù)語法如下：

lag(exp_str,offset,defval) OVER(PARTITION BY …ORDER BY …)

lead(exp_str,offset,defval) OVER(PARTITION BY …ORDER BY …)

參數(shù)說明：

exp_str是字段名

offset是偏移量，即是上1個或上N個的值，假設(shè)當(dāng)前行在表中排在第10行，則offset 為3，則表示我們所要找的數(shù)據(jù)行就是表中的第7行(即10-3=7)。

defval默認值，當(dāng)兩個函數(shù)取上N/下N個值，當(dāng)在表中從當(dāng)前行位置向前數(shù)N行已經(jīng)超出了表的范圍時，LAG()函數(shù)將defval這個參數(shù)值作為函數(shù)的返回值，若沒有指定默認值，則返回NULL，那么在數(shù)學(xué)運算中，總要給一個默認值才不會出錯。

執(zhí)行sql:

以第一行為例：4.0上一條記錄（lag）是沒有的,所有有賦予默認值0，4.0的下一條記錄（lead）還是4.0,可以通過偏移量調(diào)整上下N條記錄

注意：這里是序號的上一條或下一條

4.頭尾函數(shù)：FIRST_VALUE(expr)、LAST_VALUE(expr)

用途：返回第一個(FIRST_VALUE(expr))或最后一個(LAST_VALUE(expr))expr的值

執(zhí)行sql:

FIRST_VALUE()的結(jié)果容易理解，直接在結(jié)果的所有行記錄中輸出同一個滿足條件的首個記錄；

LAST_VALUE()默認統(tǒng)計范圍是 rows between unbounded preceding and current row，也就是取當(dāng)前行數(shù)據(jù)與當(dāng)前行之前的數(shù)據(jù)的比較。

那么如果我們直接在每行數(shù)據(jù)中顯示最后的那個數(shù)據(jù)，需在order by 條件的后面加上語句： rows between unbounded preceding and unbounded following ， 也就是前面無界和后面無界之間的行比較。

加上語句，執(zhí)行sql:

結(jié)果：

簡單理解就是，取最大的還是最小的結(jié)合ORDER BY使用，或者取第一個還是或者最后一個

參考： ;wfr=spiderfor=pc

mysql自定義函數(shù)怎么寫？

mysql CREATE FUNCTION HelloWorld4()

- RETURNS VARCHAR(20)

- BEGIN

- ? RETURN 'Hello World!';

- END;

- //

Query OK, 0 rows affected (0.00 sec)

mysql select HelloWorld4() //

+---------------+

| HelloWorld4() |

+---------------+

| Hello World! ?|

+---------------+

1 row in set (0.00 sec) ...展開mysql CREATE FUNCTION HelloWorld4()

- RETURNS VARCHAR(20)

- BEGIN

- ? RETURN 'Hello World!';

- END;

- //

Query OK, 0 rows affected (0.00 sec)

mysql select HelloWorld4() //

+---------------+

| HelloWorld4() |

+---------------+

| Hello World! ?|

+---------------+

1 row in set (0.00 sec)

Mysql 創(chuàng)建自定義函數(shù)

在使用mysql的過程中，mysql自帶的函數(shù)可能不能完成我們的業(yè)務(wù)需求，這時就需要自定義函數(shù),

函數(shù)包括數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)、格式化函數(shù)等。通過這些函數(shù)，可以簡化用戶的操作。

在MySQL——函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法一樣。