时间戳

首页 > 解决方案 > 用sql查询计算平均值

sql - 用sql查询计算平均值

问题描述

我想知道从 2018 年 3 月开始每个用户的平均小时工资。我想用 SQL 查询来计算。

我有这样的数据：

在此处输入图像描述

开始日期表示小时工资何时开始以及何时有适用小时工资的新开始日期等。

结果应该是：

2 €10,65
4 €9,90

我怎样才能在 SQL 中做到这一点？

标签： sqldateaverage

解决方案

试试下面的查询。创建一个日历表，然后cte为缺少的日期添加缺少的数据。一旦我们有缺失日期的数据，简单的过滤WHERE子句GROUPBY就会起作用。

IF OBJECT_ID ('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

IF OBJECT_ID('tempdb..#calendar') IS NOT NULL
    DROP TABLE #calendar;

CREATE TABLE #temp
(
UserId INT,
StartDate DATE,
HourlyWage decimal(10,2)
)

DECLARE @date DATE = '20180305'
INSERT INTO #temp VALUES 
(2,'20180205', 10.3),
(2,@date, 10.3),
(2,DATEADD(DAY,1,@date), 10.3),
(2,DATEADD(DAY,2,@date), 10.3),
(2,DATEADD(DAY,3,@date), 10.3),
(2,DATEADD(DAY,4,@date), 10.3),
(2,DATEADD(DAY,5,@date), 10.3),
(2,DATEADD(DAY,6,@date), 10.3),
(2,DATEADD(DAY,7,@date), 10.3),
(2,DATEADD(DAY,8,@date), 10.3),
(2,DATEADD(DAY,9,@date), 10.3),
(2,DATEADD(DAY,10,@date), 10.3),
(2,DATEADD(DAY,11,@date), 10.3),
(2,DATEADD(DAY,12,@date), 10.3),
(2,DATEADD(DAY,13,@date), 10.3),
(2,DATEADD(DAY,14,@date), 10.3),
(2,DATEADD(DAY,15,@date), 10.3),
(2,DATEADD(DAY,16,@date), 10.3),
(2,DATEADD(DAY,17,@date), 10.3),
(2,DATEADD(DAY,18,@date), 10.3),
(2,DATEADD(DAY,19,@date), 10.3),
(2,DATEADD(DAY,20,@date), 10.3),
(2,DATEADD(DAY,21,@date), 10.3),
(2,DATEADD(DAY,22,@date), 12.5),
(2,DATEADD(DAY,23,@date), 12.5),
(2,DATEADD(DAY,24,@date), 12.5),
(2,DATEADD(DAY,25,@date), 12.5),
(2,DATEADD(DAY,26,@date), 12.5),
(4,'20170221', 9.5),
(4,'20170301', 9.7),
(4,'20180227', 9.9)

DECLARE @FromDate DATETIME ,
        @ToDate DATETIME;

SELECT @FromDate = MIN(StartDate) ,
       @ToDate = MAX(StartDate)
FROM   #Temp;


SELECT   TOP ( DATEDIFF(day, @FromDate, @ToDate) + 1 ) calendarDate = CAST(DATEADD(
                                                                                 DAY ,
                                                                                 number ,
                                                                                 @FromDate) AS DATE) 
INTO     #calendar
FROM     [master].dbo.spt_values
WHERE    [type] = N'P'
ORDER BY number;

;WITH tempCTE
AS ( SELECT  DISTINCT t.userid ,
                      cal.calendarDate
     FROM    (   SELECT   calendarDate
                 FROM     #calendar c
                 ) cal
             CROSS JOIN (   SELECT UserId ,
                                   MIN(StartDate) OVER ( PARTITION BY UserId ) AS Mindate ,
                                   MAX(StartDate) OVER ( PARTITION BY UserId ) AS Maxdate 
                            FROM   #Temp ) t
     WHERE   cal.calendarDate
     BETWEEN t.Mindate AND t.Maxdate )

         SELECT   cal.UserId ,
         AVG(x.HourlyWage ) AS Avg_Wage
FROM     tempCTE cal
         CROSS APPLY (   SELECT   TOP 1 t.HourlyWage
                         FROM     #Temp t
                         WHERE    t.UserId = cal.UserId
                                  AND t.StartDate <= cal.calendarDate
                         ORDER BY t.StartDate DESC ) AS x
WHERE CalendarDate BETWEEN '20180301' AND '20180331' --your date filter here
GROUP BY cal.UserId

结果：

+--------+-----------+
| UserId | Avg_Wage  |
+--------+-----------+
|      2 | 10.654838 |
+--------+-----------+

推荐阅读