MS SQL Server
  1. MS SQL Server
  3. GROUP BY et Agrégation sur une plage de dates
Intereting Posts
Intégrité des données dans Entity Framework Générer une list de dates en fonction du jour et de l'occurrence du mois Alternatives d'audit SQL Server avec le suivi des users de l'application Comment searchr du text dans SQL Server en ignorant les caractères spéciaux? SQL Server ADO.NET sur une connection TCP / IP à faible bande passante des conseils / expériences? Intégration de database Openfire Sql L'affectation de variables n'est pas autorisée dans une instruction contenant un opérateur UNION, INTERSECT ou EXCEPT de niveau supérieur SQL DATEDIFF avec MILLISECONDS sur SubSelect SQL: Est-il possible de calculer le champ par la formule dans un autre champ comme text ServiceStack Ormlite OnDelete = "CASCADE" ne fonctionne pas Requête avec jointure à gauche et count dans les travaux sélectionnés dans MySQL mais pas dans MS SQL Server 2012 Vérification des parameters nécessaires de la procédure stockée avec PowerShell Impossible d'installer SQL Server 2014 sur Windows 7 – HRESULT 0x8007370B Transaction dissortingbuée sur server lié entre SQL Server et MySQL Comment stocker correctement datetime UTC et travailler avec un décalage local?

GROUP BY et Agrégation sur une plage de dates

 
SELECT IsConfirmed, IsNetConfirmed, d.FullDate FROM Final.FactApplication f INNER JOIN final.DimOfferedDate d on f.OfferedDateKey= d.OfferedDateKey WHERE d.CalendarYear in ('2013','2014','2015')

Le code ci-dessus renvoie les exemples de données suivants. 

 IsConfirmed IsNetConfirmed FullDate ---------------------------------------------- 1 0 2013-01-04 00:00:00.000 1 1 2013-02-04 00:00:00.000 0 1 2013-03-04 00:00:00.000 1 0 2013-04-04 00:00:00.000

Je souhaite agréger la sum de IsConfirmed et IsNetConfirmed sur le jour et le mois pour chaque année afin que je me retrouve avec le résultat suivant. Je dois agréger pour chaque année afin que la sum pour 31/04/2012 inclut datatables entre le 1/1 / 2012-4 / 31/2012. entrez la description de l'image ici

Voici mon code jusqu'à maintenant – mais je ne peux pas envelopper ma tête autour de tous les groupes. Aidez-moi, s'il vous plaît. 

 SELECT sum(IsConfirmed) AS ConfirmCount ,sum(IsNetConfirmed) AS NetConfirmCount ,year(d.fulldate) AS cyear ,month(d.fulldate) AS cmonth ,day(d.fulldate) AS cday FROM final.FactApplicationHistory f INNER JOIN final.DimOfferedDate d ON f.OfferedDateKey = d.OfferedDateKey WHERE d.CalendarYear IN ('2013','2014','2015') GROUP BY year(d.fulldate) ,month(d.fulldate) ,day(d.fulldate) ORDER BY year(d.fulldate)

Les sums en cours sont difficiles. Vous ne pouvez pas vraiment les faire avec juste un groupe par. Il y a plusieurs façons de le faire. Une façon consiste à joindre la table de données avec elle-même sur tous les loggings dont la valeur est inférieure ou égale à l'logging en cours et à additionner les points de données. Votre requête est particulièrement compliquée par le fait que vous souhaitez également faire pivoter datatables. Voici comment vous le feriez avec la méthode de jointure de table: 

 ;with temp (IsConfirmed, IsNetConfirmed, FullDate) AS ( SELECT IsConfirmed, IsNetConfirmed, FullDate FROM final.FactApplicationHistory f INNER JOIN final.DimOfferedDate d ON f.OfferedDateKey = d.OfferedDateKey WHERE d.CalendarYear IN ('2013','2014','2015') ) , pivottable (cmonthday,cmonth,cday,ConfirmCount2013,NetConfirmCount2013,ConfirmCount2014,NetConfirmCount2014,ConfirmCount2015,NetConfirmCount2015) AS ( SELECT dateadd(day,cday,(DATEADD(month, cmonth, 0))) cmonthday, cmonth, cday, sum(isnull([2013],0)) ConfirmCount2013, sum(isnull([2016],0)) NetConfirmCount2013, sum(isnull([2014],0)) ConfirmCount2014, sum(isnull([2017],0)) NetConfirmCount2014, sum(isnull([2015],0)) ConfirmCount2015, sum(isnull([2018],0)) NetConfirmCount2015 FROM (SELECT sum(IsConfirmed) AS ConfirmCount ,sum(IsNetConfirmed) AS NetConfirmCount ,year(d.FullDate) AS cyear ,year(d.FullDate)+3 AS cyear2 ,month(d.FullDate) AS cmonth ,day(d.FullDate) AS cday FROM #temp d WHERE year(FullDate) IN ('2013','2014','2015') GROUP BY year(d.FullDate) ,month(d.FullDate) ,day(d.FullDate) ) ps PIVOT ( SUM (ConfirmCount) FOR cyear IN ( [2013],[2014],[2015]) ) AS pvt PIVOT ( SUM (NetConfirmCount) FOR cyear2 IN ( [2016],[2017],[2018]) ) AS pvt Group by cmonth, cday ) select pivottable.cmonth, pivottable.cday, sum(RunningSums.ConfirmCount2013) ConfirmCount2013, sum(RunningSums.NetConfirmCount2013) NetConfirmCount2013, sum(RunningSums.ConfirmCount2014) ConfirmCount2014, sum(RunningSums.NetConfirmCount2014) NetConfirmCount2014, sum(RunningSums.ConfirmCount2015) ConfirmCount2015, sum(RunningSums.NetConfirmCount2015) NetConfirmCount2015 from pivottable join pivottable RunningSums on RunningSums.cmonthday <= pivottable.cmonthday group by pivottable.cmonth,pivottable.cday order by pivottable.cmonth, pivottable.cday

Je suppose que cela pourrait même être un cas où un slider serait en fait une bonne idée. Vous pouvez créer vos données de pivot de sortie dans une table, puis parcourir chaque logging et mettre à jour chaque valeur avec la sum en cours d'exécution. Pour une très grande table avec des millions d'loggings, cela serait probablement plus efficace que ma méthode d'auto-jointure.

Tout d'abord, vous devez générer toutes les combinaisons mois-date et année possibles. Par exemple, en 2013, vous avez des dates du 1er janvier au 5 janvier et en 2014, vous avez des dates du 3 janvier au 6 janvier. Vous devriez avoir des dates du 1er janvier au 6 janvier pour 2013 et 2014. Après avoir obtenu toutes les dates, JOIN -la avec votre requête d'origine afin que chaque date nouvellement générée ait une valeur pour IsConfirmed et IsNetConfirmed . De cela, vous obtenez le total cumulé. Enfin, vous voudrez PIVOT le total cumulé pour atteindre le résultat souhaité.

Voici une approche dynamic utilisant Crosstab. Vous pouvez lire cet article pour reference.

SQL Fiddle 

 DECLARE @sql1 VARCHAR(4000) = '', @sql2 VARCHAR(4000) = '', @sql3 VARCHAR(4000) = '' SELECT @sql1 = ';WITH SampleData AS( -- Replace this CTE with the original query SELECT * FROM Data ) ,CrossDates AS( -- Generate date combinations SELECT YY, MM, DD, FullDate = DATEADD(DAY, DD - 1, DATEADD(MONTH, MM - 1, DATEADD(YEAR, YY - 1900, 0))) FROM ( SELECT DISTINCT MM = MONTH(FullDate), DD = DAY(FullDate) FROM SampleData )DM CROSS JOIN( SELECT DISTINCT YY = YEAR(FullDate) FROM SampleData )Y ) ,CteAllDates AS( -- Assign value for each newly generated date SELECT c.*, IsConfirmed = ISNULL(s.IsConfirmed, 0), IsNetConfirmed = ISNULL(s.IsNetConfirmed, 0) FROM CrossDates c LEFT JOIN SampleData s ON s.FullDate = c.FullDate ) ,RunningTotal AS( -- Compute running total SELECT YY = YEAR(FullDate), MM = MONTH(FullDate), DD = DAY(FullDate), Confirm = SUM(CAST(IsConfirmed AS INT)) OVER(PARTITION BY YEAR(FullDate) ORDER BY MONTH(FullDate), DAY(FullDate)), NetConfirm = SUM(CAST(IsNetConfirmed AS INT)) OVER(PARTITION BY YEAR(FullDate) ORDER BY MONTH(FullDate), DAY(FullDate)) FROM CteAllDates ) SELECT MM , DD ' SELECT @sql2 = @sql2 + ' , MAX(CASE WHEN YY = ' + CONVERT(VARCHAR(4), YY) + ' THEN Confirm ELSE 0 END) AS [' + CONVERT(VARCHAR(4), YY) + ' Confirm]' + CHAR(10) + ' , MAX(CASE WHEN YY = ' + CONVERT(VARCHAR(4), YY) + ' THEN NetConfirm ELSE 0 END) AS [' + CONVERT(VARCHAR(4), YY) + ' NetConfirm]' + CHAR(10) FROM( SELECT DISTINCT YY = YEAR(FullDate) FROM( SELECT * FROM Data -- Replace this with the original query )d )t SELECT @sql3 = 'FROM RunningTotal GROUP BY MM, DD' PRINT(@sql1 + @sql2 + @sql3) EXEC(@sql1 + @sql2 + @sql3)

Remarques:

1. Remplacez SampleData par votre requête d'origine.

2. SUM() OVER() est utilisé pour get le total cumulé.

3. Fondamentalement, remplacez SELECT * FROM Data par votre requête oqriginal.

Exemple de données 

 IsConfirmed IsNetConfirmed FullDate ----------- -------------- ----------------------- 1 0 2013-04-01 00:00:00.000 1 1 2013-04-02 00:00:00.000 0 1 2013-04-03 00:00:00.000 1 0 2013-04-04 00:00:00.000 1 0 2014-04-01 00:00:00.000 1 1 2014-04-02 00:00:00.000 0 1 2014-04-03 00:00:00.000 1 0 2014-04-05 00:00:00.000

Résultat 

 |----|----|--------------|-----------------|--------------|-----------------| | MM | DD | 2013 Confirm | 2013 NetConfirm | 2014 Confirm | 2014 NetConfirm | |----|----|--------------|-----------------|--------------|-----------------| | 4 | 1 | 1 | 0 | 1 | 0 | | 4 | 2 | 2 | 1 | 2 | 1 | | 4 | 3 | 2 | 2 | 2 | 2 | | 4 | 4 | 3 | 2 | 2 | 2 | | 4 | 5 | 3 | 2 | 3 | 2 |