首页 文章

用于报告多个站点分数的滚动平均值的表设计和查询

提问于
浏览 次
0

使用MS Access 2007 .

我们为大约50个站点 Build 了一个度量标准，每月计算一次，并存储在一个表中 . 我们希望 Build 一个生成的报告，以显示上个月的分数以及趋势分别为3个月，6个月和12个月的平均值 . 首先我们设置如下表，因为使用与UNION ALL连接的四个SELECT TOP N语句，通过SQL计算滚动平均值很容易 .

score_date  site1  site2  ...  site50
date1       x1%    x2%         x50%
date2       y1%    y2%         y50%

查询计算滚动平均值：

SELECT roll, AVG(site1) AS site1Avg, AVG(site2) AS site2Avg, etc
FROM (
SELECT TOP 12 'roll12' AS roll, *
FROM tblAuditScore
ORDER BY score_date DESC) AS a
GROUP BY roll

UNION ALL

SELECT roll, AVG(site1) AS site1Avg, AVG(site2) AS site2Avg, etc
FROM (
SELECT TOP 6 'roll06' AS roll, *
FROM tblAuditScore
ORDER BY score_date DESC) AS b
GROUP BY roll

UNION ALL

etc

这适用于计算滚动平均值，但我们遇到一个生成报告的问题，因为没有univot函数，并且使用UNION ALL和PIVOT，如下面的链接所示50个站点很麻烦50个联合查询从基数中提取数据查询上面的查找最后得分，3个月，6个月和12个月的平均值（但如果需要，将使用） .

Access Union/Pivot to Swap Columns and Rows

接下来，我们查看如下所示的表格，可以使用交叉表查询轻松创建原始表格 . 但是，现在这会导致200个唯一查询，因为每个站点有四个单独的查询，以查找最后得分，3个月，6个月和12个月的平均值 . 第一种方法至少允许底层查询保持不变 .

score_date  site   score
date1       site1  x1%
date1       site2  x2%
date2       site1  y1%

由于分配给我们地区的站点会发生波动，我们目前计划将该表作为记录集循环，并在生成报告之前通过VBA创建几个大量查询 . 我们是否完全错过了一个简单的解决方案或以错误的方式接近这个？如果需要任何澄清信息，请告知我们 . 谢谢 .

Additional Info

源数据是具有输出此信息的UDF的查询

site_code  score_date  audit_score

tblAuditScore是上面两种不同的表格布局

sql ms-access ms-access-2007

2 回答

  • 0

    我已修改为使用“Iif”（而不是Case /何时是SQL Server）......我仍然没有原始数据中的列清晰列表 . 让我提出以下建议 -

    Select
    site,

    AVG(Iif Beg1Mo <= score_date 
    and score_date <= EndDate, score, null) as Last1Mo,

    AVG(Iif Beg3Mo <= score_date 
    and score_date <= EndDate, score, null) as Last3Mo,

    AVG(Iif Beg6Mo <= score_date 
    and score_date <= EndDate, score, null) as Last6Mo,

    AVG(Iif Beg12Mo <= score_date 
    and score_date <= EndDate, score, null) as Last12Mo

From raw_data 
Where    Beg12Mo <= score_date 
  and score_date <= EndDate  
Group by site

    在我的下一篇文章中担心计算Beg-date和EndDate

    回复于
  • 0

    也许有人会指出一个更好的方法来做到这一点，但这是我如何解决计算多个网站的滚动平均值 . 迭代地为每个站点运行查询 . 快速更改arrMonth字符串可以计算不同的滚动平均值 . 如果没有计算滚动平均值的足够分数（例如，六个月滚动平均值的四个分数）则不会记录 .

    tblAuditScore

    score_key           site_code  score_date  audit_score
yyyymm+<site_code>  site1      date        score

    tblAuditScoreRoll

    roll_key                 site_code  roll_period  roll_date  roll_score
yyyymm+<site_code>+<xx>  site1      period       date       score

    以上xx是滚动平均值03,06等的两位数 . 期间是您要在报表中使用的字段名称 .

    Dim rs As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim strSQLBase As String
Dim strTable As String
Dim strTableRoll As String
Dim arrMonth() As String
Dim i As Integer

strTable = "tblAuditScore" 'table to store scores
strTableRoll = "tblAuditScoreRoll" 'table to store rolling averages
arrMonth = Split("03,06,12", ",") 'modify array to contain any rolling averages desired

'open query with audit score into recordset
strSQL = "" _
    & "SELECT Format(DATE(),'yyyymm') & site_code AS score_key, " _
        & "site_code, " _
        & "DATE() AS score_date, " _
        & "audit_score " _
        & "score_weight " _
    & "FROM qryAudit_report;"
Set rs = dbLocal.OpenRecordset(strSQL)

'read all plants from query to generate multiple queries to populate table
If Not (rs.EOF And rs.BOF) Then

    'define base SQL string to be used repetitively
    strSQLBase = "" _
        & "SELECT MAX(score_key) & '<<xx>>' AS roll_key, site_code, 'roll<<xx>>' AS roll_period, MAX(score_date) AS roll_date, ROUND(AVG(audit_score),4) AS roll_score " _
        & "FROM (" _
        & "SELECT TOP <<xx>> score_key, site_code, score_date, audit_score " _
        & "FROM " & strTable & " " _
        & "WHERE site_code='<<site_code>>' " _
        & "AND score_date BETWEEN DATESERIAL(YEAR(DATEADD('m',-<<x>>,DATE())),MONTH(DATEADD('m',-<<xx>>,DATE()))+1,1) AND DATE() " _
        & "ORDER BY score_date DESC) AS u<<xx>> " _
        & "GROUP BY site_code " _
        & "HAVING COUNT(audit_score) >= ROUND(<<xx>>*5/6,0) "

    rs.MoveFirst
    Do Until rs.EOF = True

        If DCount("[score_key]", strTable, "[score_key]='" & rs!score_key & "'") = 0 Then
            strSQL = "INSERT INTO " & strTable & " (score_key, site_code, score_date, audit_score, score_weight) " _
                & "SELECT " & rs!score_key & ", " & rs!site_code & ", #" & rs!score_date & "#, " & ROUND(rs!audit_score, 4) & ", " & rs!count_AUFNR & ";"
            dbLocal.Execute strSQL, dbFailOnError

            strSQL = "" 'clear string
            'generate SQL for all rolling averages defined
            For i = LBound(arrMonth) To UBound(arrMonth)
                strSQL = strSQL & Replace(Replace(strSQLBase, "<<xx>>", arrMonth(i)), "<<site_code>>", rs!site_code) _
                    & vbNewLine & vbNewLine & "UNION ALL "
            Next i

            'remove trailing UNION ALL
            strSQL = Left(strSQL, Len(strSQL) - 14)
            'insert results into table
            strSQL = "INSERT INTO " & strTableRoll & " (roll_key, site_code, roll_period, roll_date, roll_score) " _
                & "SELECT * FROM (" & strSQL & ") AS q;"

            'create temp query to insert rolling average into table
            Set qdf = dbLocal.CreateQueryDef("")
            With qdf
                .SQL = strSQL
                .Execute dbFailOnError
                .Close
            End With

        End If

        rs.MoveNext

    Loop

'add code to export report

Else
    MsgBox "There are no records in the query to store in " & strTable
End If

rs.Close
Set rs = Nothing

    TLDR

    上面使用以下六个月的滚动平均示例循环遍历所需的每个滚动平均值的所有站点：

    SELECT MAX(score_key) & '06' AS roll_key, site_code, 'roll06' AS roll_period, MAX(score_date) AS roll_date, ROUND(AVG(audit_score),4) AS roll_score 
FROM (
SELECT TOP 06 score_key, site_code, score_date, audit_score 
FROM strTable  
WHERE site_code='<<site_code>>' 
AND score_date BETWEEN DATESERIAL(YEAR(DATEADD('m',-6,DATE())),MONTH(DATEADD('m',-6,DATE()))+1,1) AND DATE() 
ORDER BY score_date DESC) AS u
GROUP BY site_code
HAVING COUNT(audit_score) >= 5
    回复于

相关问题