SQL用于MS Access中子集的聚合25百分位数值？

Question

当字段GICS扇区=“Energy”时，这将得到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];

...which正确地生成：

• GICS部门，25 25Percentile
• 能源，-1.2

现在，我正试图为每个GICS部门获得所有的25%。看起来会是这样的：

• GICS部门，25 25Percentile
• 能源，-1.2
• 工业，一些价值
• 材料，一些值...etc。

以下是我的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];

以上...the对所有GICS扇区的值相同。我想要一个不同的25个百分位数的值，每个GICS部门。

Answer 1

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

...which是正确的。谢谢你的帮助，马克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