SQL 表示 MS Access 中子集的聚合 25% 值

Question

当字段 GICS 扇区 = "能量"时，这将获得 GM 字段的第 25 个百分位值：

SELECT
  tblFirst250.[GICS Sector]
  , 0.75*(
    SELECT Max(GM) 
    FROM tblFirst250 
    WHERE tblFirst250.GM IN (
      SELECT TOP 25 PERCENT GM 
      FROM tblFirst250 
      WHERE tblFirst250.[GICS Sector ]= "Energy" AND GM Is Not Null ORDER BY GM)) + 0.25*(
        SELECT Min(GM) 
        FROM tblFirst250 
        WHERE tblFirst250.GM IN (
          SELECT TOP 75 PERCENT GM 
          FROM tblFirst250 
          WHERE tblFirst250.[GICS Sector] = "Energy" AND GM Is Not Null ORDER BY GM DESC)
  ) AS 25Percentile
FROM tblFirst250
WHERE tblFirst250.[GICS Sector] = "Energy"
GROUP BY tblFirst250.[GICS Sector];

。它正确地产生：

• GICS部门，25百分位
• 能量，-1.2

现在，我正在尝试获取每个GICS部门的所有第25个百分位数。它看起来像这样：

• GICS部门，25百分位
• 能量，-1.2
• 工业，[一些价值]
• 材料，[一些价值]...等。

这是我的 500 次尝试之一，它无法正常工作：

SELECT tbl.[GICS Sector], 0.75*(
SELECT Max(GM) 
FROM tbl 
WHERE tbl.GM IN (
SELECT TOP 25 PERCENT GM 
FROM tbl 
WHERE tbl.[GICS Sector] = tbl.[GICS Sector] AND GM Is Not Null ORDER BY GM)) + 0.25*(
SELECT Min(GM) 
FROM tbl 
WHERE tbl.GM IN (
SELECT TOP 75 PERCENT GM 
FROM tbl 
WHERE tbl.[GICS Sector] = tbl.[GICS Sector] AND GM Is Not Null ORDER BY GM DESC)
) AS 25Percentile
FROM tbl
WHERE tbl.[GICS Sector] = tbl.[GICS Sector]
GROUP BY tbl.[GICS Sector];

。上述结果是所有GICS扇区的值相同。我想要每个 GICS 扇区不同的第 25 个百分位值。

Answer 1

我在另一个论坛上问了一个类似的问题，得到了这个答案：http://www.accessforums.net/queries/sql-aggregate-25-percentile-value-subsets-ms-53125.html#post280306

。这是正确的。感谢您的所有帮助 Mark C. 希望这可以为您节省一些时间。

这是它的内涵，但不是全部。基本上是GICS扇区的循环：

Option Compare Database
Option Explicit
Function GetTop25Percent()
'MsgBox "I AM HERE"
    Call Top25PercentGroup
End Function
Sub Top25PercentGroup()
    Dim rawdata As String
    Dim desttbl As String
    Dim rs As DAO.Recordset
    Dim tmpName As String
    Dim tmpSQL As String
    rawdata = "tbl"
    desttbl = "tblTop25PCNT"
'MsgBox "I AM HERE"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "TruncateTblTop25PCNT"
    Set rs = CurrentDb.OpenRecordset("Unique GICS Sector")
'Check to see if the recordset actually contains rows
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst 'Unnecessary in this case, but still a good habit
        Do Until rs.EOF = True
            tmpName = rs("[GICS Sector]")
'Build top 25 % SQL For each GICS Sector
            tmpSQL = "INSERT INTO " & desttbl & " ( "
            tmpSQL = tmpSQL & "[GICS Sector], [GM] ) "
            tmpSQL = tmpSQL & "SELECT TOP 25 PERCENT [GICS Sector], [GM] "
            tmpSQL = tmpSQL & "FROM " & rawdata & " "
            tmpSQL = tmpSQL & "WHERE [GM] IS NOT NULL "
            tmpSQL = tmpSQL & "AND [GICS Sector] = """ & tmpName & """"
            DoCmd.RunSQL (tmpSQL)
'Move to the next record. Don't ever forget to do this.
            rs.MoveNext
        Loop
'    Else
'        MsgBox "There are no records in the recordset."
    End If
'        MsgBox "Finished looping through records."
    rs.Close            'Close the recordset
    Set rs = Nothing    'Clean up
    DoCmd.SetWarnings True
End Sub