如何在交叉表查询中平均枢轴列 [英] How to average pivot columns in crosstab query

问题描述

[Access 2003 mdb]

[Access 2003 mdb]

TRANSFORM Sum（[Count]）

SELECT [State]，[County]，[Municipality]， [案例类型]

FROM [县 - 案例类型趋势]

WHERE [日期]> = DateAdd（'ww'， - 8，Date（））

GROUP BY [州]，[县]，[市政]，[案件类型]

PIVOT WeeksAgo（[Date]）In（"Current"，"1 Week" ;，"2周"，"3周"，"4周"，"5周"，"6周"，"7周"，"8周";

TRANSFORM Sum([Count])
SELECT [State], [County], [Municipality], [Case Type]
FROM [Counties - Case Type Trend]
WHERE [Date]>=DateAdd('ww', -8, Date())
GROUP BY [State], [County], [Municipality], [Case Type]
PIVOT WeeksAgo([Date]) In("Current","1 Week","2 Weeks","3 Weeks","4 Weeks","5 Weeks","6 Weeks","7 Weeks","8 Weeks");

上面的SQL生成13个字段，Select子句中的4个字符+ Pivot子句中函数WeeksAgo（）返回的字段。对于此查询输出，我想添加一个列，该列给出最后8个Pivot子句字段的平均值（除
[Current]之外的所有字段）。有没有简单的方法来实现这个目标？

The SQL above produces 13 fields, the 4 in the Select clause + the ones returned by the function, WeeksAgo(), in the Pivot clause. To this query output, I would like to add a column that gives an average of the last 8 Pivot clause fields (all fields except [Current]). Is there any easy way to achieve this goal?

顺便提一下，有没有更有效的方法来编码WeeksAgo函数<见下文>？我对日历周感兴趣，而不仅仅是随机的7天周。

By the way, is there a more efficient way to code the WeeksAgo function <see below>? I am interested in calendar weeks and not just random 7-day weeks.

 

功能WeeksAgo $（DateInWeek为日期）

    Dim WeekDiff％

    Dim PreDate As Date

   

    PreDate =日期+ 1

   

    Do Before PreDate = DateInWeek

        PreDate = PreDate - 1年
       

       如果工作日（PreDate）= 7则为
            WeekDiff = WeekDiff + 1

       结束如果是
   循环

   

   选择案例WeekDiff

       案例0

            WeeksAgo =" Current"

       案例1&
            WeeksAgo = WeekDiff& "周"&
       案例是< 9

            WeeksAgo = WeekDiff& "周数< b $ b       案例等等
           停止

            WeeksAgo = WeekDiff& "周数< b $ b   结束选择

结束功能

Function WeeksAgo$(DateInWeek As Date)
    Dim WeekDiff%
    Dim PreDate As Date
   
    PreDate = Date + 1
   
    Do Until PreDate = DateInWeek
        PreDate = PreDate - 1
       
        If Weekday(PreDate) = 7 Then
            WeekDiff = WeekDiff + 1
        End If
    Loop
   
    Select Case WeekDiff
        Case 0
            WeeksAgo = "Current"
        Case 1
            WeeksAgo = WeekDiff & " Week"
        Case Is < 9
            WeeksAgo = WeekDiff & " Weeks"
        Case Else
            Stop
            WeeksAgo = WeekDiff & " Weeks"
    End Select
End Function

推荐答案

这是WeeksAgo函数稍微更高效的版本：

Here is a slightly more efficient version of the WeeksAgo function:

Function WeeksAgo(DateInWeek As Date) As String
    Dim d1 As Date
    Dim d2 As Date
    Dim WeekDiff As Long
    ' Sunday on or before DateInWeek
    d1 = DateInWeek - Weekday(DateInWeek, vbMonday) Mod 7
    ' Sunday in current week
    d2 = Date - Weekday(Date, vbMonday) Mod 7
    ' Number of weeks in between
    WeekDiff = (d2 - d1) / 7
    Select Case WeekDiff
        Case 0
            WeeksAgo = "Current"
        Case 1
            WeeksAgo = WeekDiff & " Week"
        Case Else
            WeeksAgo = WeekDiff & " Weeks"
    End Select
End Function

这篇关于如何在交叉表查询中平均枢轴列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！