sql - 各分值范围内的分值百分比

Question

我有一个看起来像这样的表

对于每个评估代码，我想创建一个查询，返回特定分数范围内分数的百分比...例如

 FileYear   AssessmentCode   MarkRange   MarkPercentage

   2014        11cpssdd        0-10           5.88
   2014        11cpssdd        11-20          0
   2014        11cpssdd        21-30          0
   2014        11cpssdd        31-40          0
   2014        11cpssdd        41-50          0
   2014        11cpssdd        51-60          0
   2014        11cpssdd        61-70          0
   2014        11cpssdd        71-80          29.4
   2014        11cpssdd        81-90          52.9
   2014        11cpssdd        91-100         11.7

完全不确定该怎么做，想知道是否有人能够提供帮助？在此先感谢您的帮助。

Answer 1

您可以计算每个范围的 SUM，然后计算此 SUM 在整个 SUM 中的百分比。这是完整的工作示例:

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         [FileYear] CHAR(4)
        ,[AssessmentCode] CHAR(8)
        ,[StudentID] INT
        ,[Mark] TINYINT
    )   

    INSERT INTO @DataSource ([FileYear], [AssessmentCode], [StudentID], [Mark])
    VALUES ('2014', '11cpssdd', '34323', '75')
          ,('2014', '11cpssdd', '74666', '38')
          ,('2014', '11cpssdd', '87664', '34')
          ,('2014', '11cpssdd', '87576', '66')
          ,('2014', '11cpssdd', '23455', '87')
          ,('2014', '11cpssdd', '87654', '75')
          ,('2014', '11cpssdd', '98776', '75')
          ,('2014', '11cpssdd', '34543', '55')
          ,('2014', '11ecoeco', '87687', '89')
          ,('2014', '11ecoeco', '56466', '77')
          ,('2014', '11ecoeco', '34544', '45')
          ,('2014', '11ecoeco', '95554', '23')
          ,('2014', '11ecoeco', '22322', '56')
          ,('2014', '11ecoeco', '76557', '66')

    SELECT [FileYear]
          ,[AssessmentCode]
          ,[MarkPercentage]
          ,[MarkRange] * 100 /[ALL] AS [MarkRange]
    FROM
    (
        SELECT [FileYear]
              ,[AssessmentCode]
              ,SUM(IIF([Mark] BETWEEN 0 AND 10, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 11 AND 20, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 21 AND 30, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 31 AND 40, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 41 AND 50, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 51 AND 60, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 61 AND 70, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 71 AND 80, [Mark], 0)) 
              ,SUM(IIF([Mark] BETWEEN 81 AND 90, [Mark], 0))
              ,SUM(IIF([Mark] BETWEEN 91 AND 100, [Mark], 0)) 
              ,SUM([Mark])
        FROM @DataSource
        GROUP BY [FileYear]
                ,[AssessmentCode]
    ) DataSource ([FileYear], [AssessmentCode], [0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100], [ALL])
    UNPIVOT
    (
        [MarkRange] FOR [MarkPercentage] IN ([0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100])
    )PVT
    ORDER BY [FileYear]
            ,[AssessmentCode]
            ,[MarkPercentage]

SET NOCOUNT OFF
GO

---

检查此解决方案。请注意，我们正在过滤特定的 AssesmentCode。此外，如果您需要 decimal 格式的结果，只需在除法之前转换 [MarkRange]。

SET NOCOUNT ON
GO

    DECLARE @DataSource TABLE
    (
         [FileYear] CHAR(4)
        ,[AssessmentCode] CHAR(8)
        ,[StudentID] INT
        ,[Mark] TINYINT
    )   

    INSERT INTO @DataSource ([FileYear], [AssessmentCode], [StudentID], [Mark])
    VALUES ('2014', '11CPSSDD', '34323', '93')
          ,('2014', '11CPSSDD', '74666', '93')
          ,('2014', '11CPSSDD', '87664', '90')
          ,('2014', '11CPSSDD', '87576', '90')
          ,('2014', '11CPSSDD', '23455', '89')
          ,('2014', '11CPSSDD', '87654', '86')
          ,('2014', '11CPSSDD', '98776', '84')
          ,('2014', '11CPSSDD', '34543', '84')
          ,('2014', '11CPSSDD', '87687', '84')
          ,('2014', '11CPSSDD', '56466', '83')
          ,('2014', '11CPSSDD', '34544', '82')
          ,('2014', '11CPSSDD', '95554', '80')
          ,('2014', '11CPSSDD', '22322', '79')
          ,('2014', '11CPSSDD', '76557', '78')
          ,('2014', '11CPSSDD', '76557', '77')
          ,('2014', '11CPSSDD', '76557', '76')
          ,('2014', '11CPSSDD', '76557', '70')

    SELECT [FileYear]
          ,[AssessmentCode]
          ,[MarkPercentage]
          ,[MarkRange] * 100 /[ALL] AS [MarkRange]
    FROM
    (
        SELECT [FileYear]
              ,[AssessmentCode]
              ,SUM(IIF([Mark] BETWEEN 0 AND 10, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 11 AND 20, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 21 AND 30, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 31 AND 40, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 41 AND 50, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 51 AND 60, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 61 AND 70, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 71 AND 80, 1, 0)) 
              ,SUM(IIF([Mark] BETWEEN 81 AND 90, 1, 0))
              ,SUM(IIF([Mark] BETWEEN 91 AND 100, 1, 0)) 
              ,COUNT([Mark])
        FROM @DataSource
        WHERE [AssessmentCode] = '11CPSSDD'
        GROUP BY [FileYear]
                ,[AssessmentCode]
    ) DataSource ([FileYear], [AssessmentCode], [0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100], [ALL])
    UNPIVOT
    (
        [MarkRange] FOR [MarkPercentage] IN ([0-10], [11-20], [21-30], [31-40], [41-50], [51-60], [61-70], [71-80], [81-90], [91-100])
    )PVT
    ORDER BY [FileYear]
            ,[AssessmentCode]
            ,[MarkPercentage]

SET NOCOUNT OFF
GO