用sqlite 搞定年级排名，班级排名

魂断蓝桥

[ 本帖最后由 魂断蓝桥 于 2019-5-14 16:04 编辑 ]\n\nvbRC5BaseDlls.zip  (~ 2.7MB, current version: 5.0.68, last update: 2019-05-12  ... latest SQLite-version: 3.28)


终于更新到了3.28 可以支持窗口函数了，简单的测试了一下，我的电脑伪序号，班级排名，年级排名 数据量为6000多条的时候，
用时不超过0.50秒。


还有更多的，没太看懂文档。


SQLite supports the following 11 built-in window functions:row_number()The number of the row within the current partition. Rows are numbered starting from 1 in the order defined by the ORDER BY clause in the window definition, or in arbitrary order otherwise.rank()The row_number() of the first peer in each group - the rank of the current row with gaps. If there is no ORDER BY clause, then all rows are considered peers and this function always returns 1.dense_rank()

地址是： [url]https://www.sqlite.org/windowfunctions.html#wchaining[/url]





Option Explicit


Sub A()
    Dim CNN As New cConnection
    Dim RS As New cRecordset
    Dim ARR, I%, J%, SQL$, tim
    tim = Timer
    Application.ScreenUpdating = False
    ARR = Sheet2.[A1].CurrentRegion
    CNN.CreateNewDB
    SQL = "CREATE TABLE T1 (ID INTEGER PRIMARY KEY,班级 TEXT,成绩 DOUBLE)"
    CNN.Execute SQL
    CNN.BeginTrans
    For I = 2 To UBound(ARR)
        SQL = "INSERT INTO T1 (班级,成绩) VALUES('" & ARR(I, 1) & "'," & ARR(I, 2) & ")"
        CNN.Execute SQL
    Next
    CNN.CommitTrans
    Sheet2.Activate
    [D:Z].Clear
   SQL = "SELECT 班级,成绩," _
                & "row_number() OVER (ORDER BY 成绩 DESC) AS 伪序号" _
                & ",RANK() OVER (ORDER BY 成绩 DESC) AS 年级排名" _
                & ",RANK () OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级排名 FROM T1 ORDER BY 1,2 DESC"
    RS.OpenRecordset SQL, CNN
    For I = 0 To RS.Fields.Count - 1
        Cells(1, I + 4) = RS.Fields(I).Name
    Next
    Range("D2").CopyFromRecordset RS.GetADORsFromContent
    Set RS = Nothing
    Set CNN = Nothing
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("D1").CurrentRegion, , xlYes).Name = "表2"
    ActiveSheet.ListObjects("表2").TableStyle = "TableStyleMedium22"
    Range("D1").CurrentRegion.AutoFilter
    Range("D:H").Font.Name = "微软雅黑"
    Range("D:H").Font.Size = 11
    Application.ScreenUpdating = True
   MsgBox Format(Timer - tim, "0.00")
End Sub

zpy2

谢谢分享，用chrome翻译了一下，慢慢再研究下。

3.内置窗口功能

除了聚合窗口函数，SQLite还具有一组基于 PostgreSQL支持的内置窗口函数 。

内置窗口函数以与聚合窗口函数相同的方式遵循任何PARTITION BY子句 - 每个选定的行都分配给一个分区，每个分区都是单独处理的。下面描述了任何ORDER BY子句影响每个内置窗口函数的方式。一些窗口函数（rank（），dense_rank（），percent_rank（）和ntile（））使用“对等组”的概念（同一分区中的行对所有ORDER BY表达式具有相同的值）。在这些情况下，frame-spec是指定ROWS，GROUPS还是RANGE 并不重要。出于内置窗口函数处理的目的，无论帧类型如何，所有ORDER BY表达式具有相同值的行都被视为对等。

大多数内置窗口函数忽略 frame-spec，例外是first_value（），last_value（）和nth_value（）。将FILTER子句指定为内置窗口函数调用的一部分是语法错误。

SQLite支持以下11种内置窗口函数：

ROW_NUMBER（）

当前分区中的行数。行按照窗口定义中ORDER BY子句定义的顺序从1开始编号，否则按任意顺序编号。

秩（）

每个组中第一个对等体的row_number（） - 具有间隙的当前行的等级。如果没有ORDER BY子句，则所有行都被视为对等，并且此函数始终返回1。

DENSE_RANK（）

其分区中当前行的对等组的编号 - 当前行的无间隙的等级。分区从窗口定义中的ORDER BY子句定义的顺序开始编号。如果没有ORDER BY子句，则所有行都被视为对等，并且此函数始终返回1。

PERCENT_RANK（）

尽管名称，此函数始终返回介于0.0和1.0之间的值等于（rank - 1）/（partition-rows - 1），其中 rank是内置窗口函数rank（）返回的值，而分区行是分区中的总行数。如果分区只包含一行，则此函数返回0.0。

CUME_DIST（）

累积分布。计算为 行号 / 分区行，其中row-number是row_number（）为组中的最后一个对等项返回的值，而partition-rows是分区中的行数。

NTILE（N）

参数N作为整数处理。此函数尽可能均匀地将分区划分为N个组，并按ORDER BY子句定义的顺序为每个组分配1到N之间的整数，否则按任意顺序分配。如有必要，首先会出现较大的群体。此函数返回分配给当前行所属的组的整数值。

滞后（expr）
滞后（expr，偏移）
滞后（expr，偏移，默认）

lag（）函数的第一种形式返回计算表达式expr与分区中前一行的结果。或者，如果没有前一行（因为当前行是第一行），则为NULL。

如果提供了offset参数，则它必须是非负整数。在这种情况下，返回的值是针对分区中当前行之前的行偏移行计算expr的结果。如果offset为0，则 针对当前行计算expr。如果当前行之前没有行 偏移行，则返回NULL。

如果还提供了default，则如果由offset标识的行不存在，则返回NULL而不是NULL 。

lead（expr）
lead（expr，offset）
lead（expr，offset，default）

lead（）函数的第一种形式返回计算表达式expr对分区中下一行的结果。或者，如果没有下一行（因为当前行是最后一行），则为NULL。

如果提供了offset参数，则它必须是非负整数。在这种情况下，返回的值是对分区中当前行之后的行偏移行计算expr的结果。如果offset为0，则 针对当前行计算expr。如果当前行之后没有行 偏移行，则返回NULL。

如果还提供了default，则如果由offset标识的行不存在，则返回NULL而不是NULL 。

FIRST_VALUE（表达式）

此内置窗口函数以与聚合窗口函数相同的方式计算每行的窗口框架。它返回针对每行的窗口框架中第一行计算的expr值。

LAST_VALUE（表达式）

此内置窗口函数以与聚合窗口函数相同的方式计算每行的窗口框架。它返回expr的值，该值是针对每行的窗口框架中的最后一行计算的。

nth_value（expr，N）

此内置窗口函数以与聚合窗口函数相同的方式计算每行的窗口框架。它返回针对窗口框架的行N评估的expr的值。行数在窗口框架内以ORDER BY子句定义的顺序从1开始编号（如果存在），或者以任意顺序编号。如果分区中没有第N行，则返回NULL。

本节中的示例均假设以下数据：

CREATE TABLE t2（a，b）;
插入t2 VALUES（'a'，'one'），
                     （'a'，'two'），
                     （'a'，'三'），
                     （'b'，'四'），
                     （'c'，'五'），
                     （'c'，'six'）;
以下示例说明了五个排名函数的行为 - row_number（），rank（），dense_rank（），percent_rank（）和cume_dist（）。

- 以下SELECT语句返回：
-
-  a | row_number | 排名| dense_rank | percent_rank | CUME_DIST
-------------------------------------------------- ----------------
-  a | 1 | 1 | 1 | 0.0 | 0.5
-  a | 2 | 1 | 1 | 0.0 | 0.5
-  a | 3 | 1 | 1 | 0.0 | 0.5
-  b | 4 | 4 | 2 | 0.6 | 0.66
-  c | 5 | 5 | 3 | 0.8 | 1.0
-  c | 6 | 5 | 3 | 0.8 | 1.0
-
选择一个AS a，
       row_number（）赢得AS row_number，
       rank（）赢得AS排名，
       dense_rank（）胜过AS dense_rank，
       percent_rank（）胜过AS percent_rank，
       cume_dist（）赢得AS cume_dist
从t2开始
WINDOW赢得AS（ORDER BY a）;
下面的例子使用ntile（）将六行分成两组（ntile（2）调用）和四组（ntile（4）调用）。对于ntile（2），每组分配三行。对于ntile（4），有两组，每组两组。较大的两组首先出现。

- 以下SELECT语句返回：
-
-  a | b | ntile_2 | ntile_4
----------------------------------
-  a | 一个| 1 | 1
-  a | 两个| 1 | 1
-  a | 三个| 1 | 2
-  b | 四个| 2 | 2
-  c | 五| 2 | 3
-  c | 六| 2 | 4
-
选择一个AS a，
       b AS b，
       ntile（2）赢得了ntile_2，
       ntile（4）赢得AS ntile_4
从t2开始
WINDOW赢得AS（ORDER BY a）;
下一个示例演示了lag（），lead（），first_value（），last_value（）和nth_value（）。所述帧规格由两个滞后（）和铅（）被忽略，但通过FIRST_VALUE（），LAST_VALUE（）和nth_value（）尊重。

- 以下SELECT语句返回：
-
-  b | 铅| 滞后| first_value | last_value | nth_value_3
-------------------------------------------------- -----------
-  A | C | NULL | A | A | NULL        
-  B | D | A | A | B | NULL        
-  C | E | B | A | C | C           
-  D | F | C | A | D | C           
-  E | G | D | A | E | C           
-  F | 不适用| E | A | F | C           
-  G | 不适用| F | A | G | C           
-
SELECT b AS b，
       领先（b，2，'n / a'）赢得AS领先，
       滞后（b）赢得AS滞后，
       first_value（b）赢得AS first_value，
       last_value（b）赢得AS last_value，
       nth_value（b，3）赢得AS nth_value_3
从t1
WINDOW赢得AS（无限制前进和当前行之间的排序）

zpy2

zpy2 发表于 2019-5-14 19:46
谢谢分享，用chrome翻译了一下，慢慢再研究下。

3.内置窗口功能

收藏
https://www.sqlite.org/windowfunctions.html#wchaining
vbRichClient5
http://club.excelhome.net/thread-1358617-1-1.html
VSFlexGrid Pro 8.0
http://club.excelhome.net/thread-1454586-1-1.html
sqlite odbc
http://club.excelhome.net/forum. ... 805&page=1&

魂断蓝桥

zpy2 发表于 2019-5-14 19:46
谢谢分享，用chrome翻译了一下，慢慢再研究下。

3.内置窗口功能

除了几个排名的函数，其余的用处多吗？有点复杂啊
可以上此网站下载图形界面的sqlite，不过不支持加密，简单测试一下sql语句还是很方便的。
https://sqlitebrowser.org/dl/



简单的研究了一下排名函数，可以按照美式排名，或者是中国式排名。

数据结构如下：

ID        班级        成绩
1        01        100.0
2        01        100.0
3        01        72.0
5        02        95.0
7        02        95.0
8        03        98.0
9        03        98.0
10        03        88.0


sql语句如下

SELECT 班级,成绩,
row_number() OVER () AS 连续序号,
row_number() OVER (partition by 班级 order by 成绩 desc) AS 班级成绩序号,
RANK()  OVER (order by 成绩 DESC)  AS 年级美式排名,
dense_rank() OVER (order by 成绩 DESC)  AS 年级中国式排名,
dense_rank() OVER (PARTITION BY 班级 ORDER BY 成绩 DESC)  AS 班级中国式排名,
RANK () OVER (PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级美式排名
FROM T1
ORDER BY 班级,成绩 DESC


返回结果：

班级	成绩	连续序号	班级成绩序号	年级美式排名	年级中国式排名	班级中国式排名	班级美式排名
1	100	1	1	1	1	1	1
1	100	2	2	1	1	1	1
1	72	3	3	8	5	2	3
2	95	4	1	5	3	1	1
2	95	5	2	5	3	1	1
3	98	6	1	3	2	1	1
3	98	7	2	3	2	1	1
3	88	8	3	7	4	2	3

zpy2

总共6706条记录，在我手机端虚拟机上跑的，连界面渲染1500行，基本上是1秒，性能的确是不错。

zpy2

zpy2 发表于 2019-5-15 21:23
总共6706条记录，在我手机端虚拟机上跑的，连界面渲染1500行，基本上是1秒，性能的确是不错。

可以求出 running total运行余额，很强大，速度快。

魂断蓝桥

zpy2 发表于 2019-5-16 05:48
可以求出 running total运行余额，很强大，速度快。

没太看懂，能把原始数据发一下。谢谢

zpy2

魂断蓝桥 发表于 2019-5-16 09:23
没太看懂，能把原始数据发一下。谢谢

a FILTER clause is provided, then only rows for which the expr is true are included in the window frame. The aggregate window still returns a value for every row, but those for which the FILTER expression evaluates to other than true are not included in the window frame for any row. For example:

-- The following SELECT statement returns:
--
--   c     | a | b | group_concat
---------------------------------
--   one   | 1 | A | A           
--   two   | 2 | B | A           
--   three | 3 | C | A.C         
--   one   | 4 | D | A.C.D      
--   two   | 5 | E | A.C.D      
--   three | 6 | F | A.C.D.F     
--   one   | 7 | G | A.C.D.F.G   
--
SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
  ORDER BY a
) AS group_concat
FROM t1 ORDER BY a;

我是把这里的 group_CONCAT 改成Sum,发现的除了合并显示求和也可以。

zpy2

zpy2 发表于 2019-5-16 09:42
a FILTER clause is provided, then only rows for which the expr is true are included in the window  ...

http://club.excelhome.net/forum.php?mod=viewthread&tid=1477408&fromguid=hot&extra=
和这个有点类似

魂断蓝桥

zpy2 发表于 2019-5-16 09:45
http://club.excelhome.net/forum.php?mod=viewthread&tid=1477408&fromguid=hot&extra=
和这个有点类似

好的，谢谢。