中位数——分组行号之应用

roych

可能由于强迫症吧，很多人对于分组行号都有着深深的迷恋。为此，两年前也写过一篇相关的帖子：【新手入门】之二十二：分组行号
不过我一直不知道这个有什么应用。今天在某论坛上看到有人提及中位数的计算，忽然觉得，也许这就是其中的一个应用吧。

中位数，通常需要先按一定的规则排序，然后在排序后的记录里找出最中间的一个（记录数为奇数）或者两个（记录数为偶数），然后求均值，即为中位数。如果需要分类别处理，那么前面提及的分组行号就派上用场了。

操作顺序如下：
1、根据条件（比如，按给定时间区间）生成查询结果表。
2、把1的结果追加到临时表。
3、更新临时表的分组行号。
4、求出临时表最中间的行号。这时候我们可以用高斯求和定律来计算，用(最大值+最小值)/2即为最中间的行号。
5、根据4的结果是整数还是小数来求出实际区间。如果是整数，说明记录数恰好在中间位置。直接查临时表中这个行号的数据即可；如果是小数，说明这个数介乎两个最中间的数值中间，因此需要±0.5来处理。

说完顺序后，下面就是处理过程了。简要说明下各个组件：
data——原始数据
qry_Detail——查询结果表，对应上述的操作步骤1
median——临时表，对应上述的操作步骤2，数据结构上比原始数据多一个”排序“字段。
qry_position——位置表，对应上述的操作步骤4和5，确定起止位置。用于最终结果的条件查询。
qry_result——结果表，求出指定条件下，各个商品ID的中位数。
frm_Main——主窗体，用于自动化操作步骤1-5。
frm_Detail——展示结果表的窗体。
mod_Median——主体过程，用于生成分组序号。代码如下：

1. Sub updateRowID()
   
2.     Dim rst As New ADODB.Recordset
   
3.     Dim strGroup As String
   
4.     Dim lngPosition As Long
   
5.    
   
6.     DoCmd.SetWarnings False
   
7.     DoCmd.RunSQL "delete * from  median"
   
8.     DoCmd.RunSQL "insert into median select *, 0 as 排序 from qry_Detail order by 日期,商品ID"
   
9.    
   
10.     rst.Open "select * from median", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
11.     '初始化
    
12.     lngPosition = 0
    
13.     strGroup = rst("商品ID")
    
14.    
    
15.     Do Until rst.EOF
    
16.         If rst("商品ID") = strGroup Then
    
17.             lngPosition = lngPosition + 1
    
18.             rst("排序") = lngPosition
    
19.         Else
    
20.             rst("排序") = 1
    
21.             lngPosition = 1
    
22.             strGroup = rst("商品ID")
    
23.         End If
    
24.         rst.Update
    
25.         rst.MoveNext
    
26.     Loop
    
27.    
    
28.    rst.Close
    
29.    Set rst = Nothing
    
30. End Sub

复制代码

其它就不一一解释了。具体请查看附件。

tmtony

中位数我还是帮小孩做题时才理解

aslxt

研究了好久,才发现:楼主查询的是日期为中位数的日子的平均销量.

aslxt

[刷一下存在感]做一个自定义函数,再建一个查询就可以了,没那么复杂:
Public Function GetMidVal(groupVal, sDate As Date, eDate As Date) As Variant
'返回中位数
'groupVal:分组字段的某个值
'sDate:指定统计期间的开始日期
'eDate:指定统计期间的结束日期
    Dim Rec As New ADODB.Recordset
    Dim i As Long, N1, N2
    '按照条件查询记录集
    Rec.Open "select * from data where 商品ID='" & groupVal & "' and 日期 between #" & sDate & "# and #" & eDate & "# order by 销量", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    Select Case Rec.RecordCount
    Case Is > 1    '统计期间有销售记录
        If Rec.RecordCount Mod 2 = 0 Then    '记录数为偶数
            For i = 1 To Int(Rec.RecordCount / 2) - 1    '把指针移动到记录集正中间偏上的位置
                Rec.MoveNext
            Next i
            N1 = Rec.Fields("销量")    '
            Rec.MoveNext    '把指针移动到记录集中间偏下的位置
            N2 = Rec.Fields("销量")
            GetMidVal = (N1 + N2) / 2    '获得平均值
        Else    '记录数为奇数
            For i = 1 To 1 + Int(Rec.RecordCount / 2)    '把指针移动到记录集的中间
                Rec.MoveNext
            Next i
            GetMidVal = Rec.Fields("销量")
        End If
    Case 1
        GetMidVal = Rec.Fields("销量")
    Case 0    '统计期间没有销售记录
        GetMidVal = 0
    End Select
End Function

查询SQL:
SELECT data.商品ID, GetMidVal([商品ID],#3/3/2021#,#4/2/2021#) AS 中位数 FROM data GROUP BY data.商品ID