Groupés 7 jours vs traînante Hier serveur SQL

voix
1

J'ai un exemple de schéma dans SQL server. La forme abrégée est la suivante:

date       group   value
2017-01-01  a      10.00 
.
.
2017-01-08  a      15.00 
2017-01-01  b      10.00 
.
.
2017-01-08  b      15.00 
2017-01-01  c      10.00 
.
.
2017-01-08  c      15.00 

Je voudrais avoir ce qui suit, est aujourd'hui en supposant 2017-01-08et difference= yesterday-trail_7

yesterday     group   trail_7  yesterday   difference
2017-01-07     a       10.71      15.00       4.29
2017-01-07     b       10.71      15.00       4.29
2017-01-07     c       10.71      15.00       4.29

Dans ce qui précède, trail_7= average(values of the last 7 days)alias six 10s et un 15

solution 1

Ce qui suit travaillé avec l'exemple de violon

Declare @ReportDate date
set @ReportDate = '2017-01-08'

SELECT DATEADD(DAY, -1, @ReportDate) AS yesterday,
       [group],
       AVG(e.[value]) AS Trail_7,
       y.[value] AS yesterday, --I don't recommend having 2 columsn with the same name
       y.[value] - AVG(e.[value]) AS difference
FROM example e
     CROSS APPLY (SELECT sq.[value]
                  FROM example sq
                  WHERE sq.[group] = e.[group]
                    AND sq.days_date = DATEADD(DAY, -1,@ReportDate)) y
WHERE e.[days_date] >= DATEADD(DAY, -7, @ReportDate)
  AND e.[days_date] < DATEADD(DAY, 0, @ReportDate)
GROUP BY e.[group], y.[value];

solution 2

with atable as (select t.*,yesterday-trail_7 diff
from (select [days_date],[group]
      ,lag(value) over(partition by [group] order by [days_date]) as yesterday
      ,avg(value) over(partition by [group] order by [days_date] rows between 7 preceding and 1 preceding) as trail_7
      from example
     ) t) 
select *
from atable
where days_date = '2017-01-08'
Créé 09/03/2018 à 12:09
utilisateur
Dans d'autres langues...                            


4 réponses

voix
2

En supposant que vous avez aucune date manquantes dans le tableau, vous pouvez utiliser AVGet LAGfonctions de la fenêtre.

select t.*,yesterday-trail_7
from (select [Date],[Group]
      ,lag(value) over(partition by [Group] order by [Date]) as yesterday
      ,avg(value) over(partition by [Group] order by [Date] rows between 7 preceding and 1 preceding) as trail_7
      from tbl
     ) t
Créé 09/03/2018 à 12:28
source utilisateur

voix
0

Voici ma réponse:

declare @t table (dte date,grp varchar(10),val decimal(18,2))
insert into @t
values
    (   '1/1/2017'  ,   'a' ,   10  )
,   (   '1/2/2017'  ,   'a' ,   10  )
,   (   '1/3/2017'  ,   'a' ,   10  )
,   (   '1/4/2017'  ,   'a' ,   10  )
,   (   '1/5/2017'  ,   'a' ,   10  )
,   (   '1/6/2017'  ,   'a' ,   10  )
,   (   '1/7/2017'  ,   'a' ,   10  )
,   (   '1/8/2017'  ,   'a' ,   15  )
,   (   '1/9/2017'  ,   'a' ,   15  )


declare @today date = '1/9/2017'
declare @yest date = dateadd(d,-1,@today)
declare @start date = dateadd(d,-7,@yest)

;with cte as --easier to do math on diff
(
select grp
    ,[email protected]
    ,trail7 = (select avg(val) from @t tsub where dte between @start and @yest and tsub.grp=t.grp)
    ,yestVal = (select val from @t tsub where dte = @yest and tsub.grp=t.grp)
from @t t
where [email protected]
)

select cte.*
    , differnce=yestVal-trail7
from cte
Créé 09/03/2018 à 12:29
source utilisateur

voix
0

C'est en quelque sorte une supposition, que je ne sais pas comment vous obtenez la valeur 10pour en trail_7fonction des données d'échantillons fournis:

DECLARE @ReportDate date = '20170108';

SELECT DATEADD(DAY, -1, @ReportDate) AS yesterday,
       [group],
       AVG(e.[value]) AS Trail_7,
       y.[value] AS yesterday, --I don't recommend having 2 columsn with the same name
       y.[value] - AVG(e.[value]) AS difference
FROM example e
     CROSS APPLY (SELECT sq.[value]
                  FROM example sq
                  WHERE sq.[group] = e.[group]
                    AND sq.days_date = DATEADD(DAY, -1,@ReportDate)) y
WHERE e.[days_date] >= DATEADD(DAY, -7, @ReportDate)
  AND e.[days_date] < DATEADD(DAY, 0, @ReportDate)
GROUP BY e.[group], y.[value];

Si vous pouviez expliquer mieux, ce serait très utile.

Créé 09/03/2018 à 12:32
source utilisateur

voix
0

Je pense que c'est ce que vous êtes après:

select cast((getutcdate()-1) as date) yesterday
, [group]
, [trail_7]
, [trail_1] --aka yesterday, but we used than name above
, [trail_1] - [trail_7] [difference]
FROM
(
    SELECT [group]
    , avg(case 
        when [days_date] = cast((getutcdate()-1) as date) then [value] 
        else null --null is not counted in the average
    end) [trail_1]
    , avg([value]) [trail_7]
    FROM example
    WHERE [days_date] >= cast(getutcdate()-7 as date)
    group by [group]
) x
order by [group]

SQL Fiddle: http://sqlfiddle.com/#!18/dd89c/1

Créé 09/03/2018 à 13:04
source utilisateur

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more