MS SQL Server
  1. MS SQL Server
  3. Sélection et utilisation d'opérateurs par groupe de partitions
Intereting Posts
Compte SQL et duplicates de groupe DatabaseMetaData.getColumns renvoie des colonnes de tables nommées similaires Comment vérifier si une colonne est une expression de transformation de colonne dérivée de ssis entière? Comment faire à la fois le filtrage et le groupage listview à partir de SQL Utilisation d'Identity Insert sur un server lié Valeur de colonne d'parsing basée sur des délimiteurs La request a échoué ou le service n'a pas répondu dans un timeout raisonnable dans SQL Server 2008 R2 Erreur Datetime 'Conversion failed' lors de l'utilisation de la comparaison CASE Comment utiliser assembly sans nom fort dans SSIS mssql login python Impossible de convertir la valeur du paramètre d'une string en date et heure IDENTITY INSERT ne fonctionne pas avec MERGE Avantages de la database Accès, MySQL, msSQL, ou autres? Même index qui diffère uniquement sur les colonnes INCLUDE-d? joindre avec le sur fourni plus tard

Sélection et utilisation d'opérateurs par groupe de partitions

J'ai le tableau suivant qui stocke datatables d'events sportifs. J'essaie de find quels events ont augmenté en présence à mon return dans le même pays. 

+----------+------------+-----------+-------------+------------+ | EventId | EventName | Country | Attendance | EventDate | +----------+------------+-----------+-------------+------------+ | 1 | Soccer1 | Australia | 12000 | 2015-01-01 | | 2 | Soccer2 | Mexico | 35999 | 2016-02-02 | | 3 | Soccer3 | Australia | 13999 | 2015-03-22 | | 4 | Football1 | Japan | 13555 | 2003-11-12 | | 5 | Football2 | Japan | 12222 | 2004-01-01 | | 6 | Football3 | Canada | 13444 | 2003-02-23 | | 7 | Tennis1 | America | 10000 | 2014-01-02 | | 8 | Tennis2 | America | 12111 | 2015-10-01 | +----------+------------+-----------+-------------+------------+ CREATE TABLE [dbo].[Sports]( [EventId] [int] NULL, [EventName] [varchar](50) NULL, [Country] [varchar](50) NULL, [Attendance] [int] NULL, [EventDate] [date] NULL INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (1, N'Soccer1', N'Australia', 12000, CAST(N'2015-01-01' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (2, N'Soccer2', N'Mexico', 35999, CAST(N'2016-02-02' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (3, N'Soccer3', N'Australia', 13999, CAST(N'2015-03-22' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (4, N'Football1', N'Japan', 13555, CAST(N'2003-11-12' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (5, N'Football2', N'Japan', 12222, CAST(N'2004-01-01' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (6, N'Football3', N'Canada', 13444, CAST(N'2003-02-23' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (7, N'Tennis1', N'America', 10000, CAST(N'2014-01-02' AS Date)) INSERT [dbo].[Sports] ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (8, N'Tennis2', N'America', 12111, CAST(N'2015-10-01' AS Date))

J'ai donc créé un CTE pour find quels pays ont tenu un événement plus d'une fois et j'ai ensuite essayé d'utiliser la fonction lead commandée par eventdate pour voir si la date la plus récente avait une plus grande participation.

Mais lorsque ces conditions sont remplies, je veux afficher à la fois la ligne avec la fréquentation la plus élevée et la ligne avec l'événement précédent aussi. Ma tentative montre uniquement la ligne avec la date la plus récente. 

 with t as ( select EventId, EventName, Country, Attendance, CONVERT(VARCHAR(11),EventDate,106) as Date from Sports where Country in (select Country from Sports group by Country having count(*)>=2) ), s as ( select *, LEAD(Attendance) over (partition by Country order by cast([Date] as datetime) desc) as PrevAttendance from t ) select EventName, Country, Attendance, Date from s where Attendance > PrevAttendance

Donc, ma question est aussi, en général, comment effectuer des opérations sur chaque partition / window définie dans SQL Server 2014. Parce que dans cet exemple, s'il y avait beaucoup plus d'events par pays, je pourrais aussi vouloir vérifier si la fréquentation la plus récente est au less plus grand que tous les précédents, pas seulement l'événement le plus immédiatement précédent.

J'espère que cela a du sens, merci d'avance pour toute aide.

Donc, mon résultat attendu pour ce tableau serait le suivant: 

 +----------+------------+-----------+-------------+------------+ | EventId | EventName | Country | Attendance | EventDate | +----------+------------+-----------+-------------+------------+ | 1 | Soccer1 | Australia | 12000 | 2015-01-01 | | 3 | Soccer3 | Australia | 13999 | 2015-03-22 | | 7 | Tennis1 | America | 10000 | 2014-01-02 | | 8 | Tennis2 | America | 12111 | 2015-10-01 | +----------+------------+-----------+-------------+------------+

Utilisez ROW_NUMBER pour get les deux lignes les plus récentes. 

 with cte as ( select *, lead(Attendance) -- next row over (partition by Country order by EventDate desc) as PrevAttendance, lag(Attendance) -- previous row over (partition by Country order by EventDate desc) as NextAttendance, ROW_NUMBER() over (partition by Country order by EventDate desc) as rn from sports ) select * from cte -- only the last two rows -- when last row's value is greater than previous row's value where (rn = 1 and Attendance > PrevAttendance) or (rn = 2 and Attendance < NextAttendance)

Utilisez un MIN cumulatif pour find la valeur minimale sur toutes les lignes précédentes: 

 MIN(Attendance) over (partition by Country order by EventDate desc -- all rows before the current row rows between unbounded preceding and 1 preceding) 
 SET NOCOUNT ON; CREATE TABLE #sports( [EventId] [int] NULL, [EventName] [varchar](50) NULL, [Country] [varchar](50) NULL, [Attendance] [int] NULL, [EventDate] [date] NULL ); INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (1, N'Soccer1', N'Australia', 12000, CAST(N'2015-01-01' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (2, N'Soccer2', N'Mexico', 35999, CAST(N'2016-02-02' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (3, N'Soccer3', N'Australia', 13999, CAST(N'2015-03-22' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (4, N'Football1', N'Japan', 13555, CAST(N'2003-11-12' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (5, N'Football2', N'Japan', 12222, CAST(N'2004-01-01' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (6, N'Football3', N'Canada', 13444, CAST(N'2003-02-23' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (7, N'Tennis1', N'America', 10000, CAST(N'2014-01-02' AS Date)) INSERT INTO #sports ([EventId], [EventName], [Country], [Attendance], [EventDate]) VALUES (8, N'Tennis2', N'America', 12111, CAST(N'2015-10-01' AS Date)) ;WITH cte AS ( SELECT *, pat=CASE WHEN LAG(Attendance) OVER (PARTITION BY Country ORDER BY EventId)IS NULL THEN 0 WHEN Attendance<LAG(Attendance) OVER (PARTITION BY Country ORDER BY EventId) THEN 0 ELSE 1 END, patid=LAG(EventId) OVER (PARTITION BY Country ORDER BY EventId) FROM #sports ) SELECT [EventId], [EventName], [Country], [Attendance], [EventDate] FROM cte WHERE pat=1 UNION SELECT inn.[EventId], inn.[EventName], inn.[Country], inn.[Attendance], inn.[EventDate] FROM cte AS ou INNER JOIN cte AS inn ON inn.EventId=ou.patid WHERE ou.pat=1 ORDER BY EventId; DROP TABLE #sports;

Résultats: 

 +---------+-----------+-----------+------------+------------+ | EventId | EventName | Country | Attendance | EventDate | +---------+-----------+-----------+------------+------------+ | 1 | Soccer1 | Australia | 12000 | 2015-01-01 | | 3 | Soccer3 | Australia | 13999 | 2015-03-22 | | 7 | Tennis1 | America | 10000 | 2014-01-02 | | 8 | Tennis2 | America | 12111 | 2015-10-01 | +---------+-----------+-----------+------------+------------+