June 9, 2014 at 2:44 pm
Salut,
voila j'ai les données suivantes :
INSERT INTO Sales(mnth, qty) VALUES
('20071201', 100),
('20080101', 110),
('20080201', 120),
('20080301', 130),
('20080401', 140),
('20080501', 140),
('20080601', 130),
('20080701', 120),
('20080801', 110),
('20080901', 100),
('20081001', 110),
('20081101', 100),
('20081201', 120),
('20090101', 130),
('20090201', 140),
('20090301', 100),
('20090401', 100),
('20090501', 100),
('20090601', 110),
('20090701', 120),
('20090801', 110),
('20090901', 120),
('20091001', 130),
('20091101', 140),
('20091201', 100);
GO
et j'aimerais faire un regroupement sur interval de date afin d'avoir le résultat suivant :
Start_Range End_Range Trend
200712 200712 unknown
200801 200804 UP
200805 200805 SAME
200806 200809 DOWN
200810 200810 UP
200811 200811 DOWN
200812 200812 UP
200903 200903 DOWN
200904 200905 SAME
200906 200907 UP
200908 200908 DOWN
200909 200911 UP
200912 200912 DOWN
Merci pour vos réponses
Aurel
June 9, 2014 at 3:44 pm
Premièrement, je ne parle pas français. Je n'ai pas une solution complète mais cela devrait aider...
passer à l'anglais(switching to English)...
OK. You have the following data:
-- données de l'échantillon
IF OBJECT_ID('tempdb..sales') IS NOT NULL DROP TABLE sales;
GO
CREATE TABLE Sales (mnth bigint primary key, qty int);
GO
INSERT INTO Sales(mnth, qty) VALUES
('20071201', 100),
('20080101', 110),
('20080201', 120),
('20080301', 130),
('20080401', 140),
('20080501', 140),
('20080601', 130),
('20080701', 120),
('20080801', 110),
('20080901', 100),
('20081001', 110),
('20081101', 100),
('20081201', 120),
('20090101', 130),
('20090201', 140),
('20090301', 100),
('20090401', 100),
('20090501', 100),
('20090601', 110),
('20090701', 120),
('20090801', 110),
('20090901', 120),
('20091001', 130),
('20091101', 140),
('20091201', 100);
GO
...and you want to group on date interval to produce the following result:
WITH expected_results(Start_Range, End_Range, Trend)
AS
(
SELECT 200712, 200712, 'unknown' UNION ALL
SELECT 200801, 200804,'UP' UNION ALL
SELECT 200805, 200805,' SAME' UNION ALL
SELECT 200806, 200809,' DOWN' UNION ALL
SELECT 200810, 200810,' UP' UNION ALL
SELECT 200811, 200811,' DOWN' UNION ALL
SELECT 200812, 200812,' UP' UNION ALL
SELECT 200903, 200903,' DOWN' UNION ALL
SELECT 200904, 200905,' SAME' UNION ALL
SELECT 200906, 200907,' UP' UNION ALL
SELECT 200908, 200908,' DOWN' UNION ALL
SELECT 200909, 200911,' UP' UNION ALL
SELECT 200912, 200912,' DOWN'
)
SELECT *
FROM expected_results;
I worked on this a little and think this will get you closer. Again, not a complete solution but something to start with.
-- ce, encore une fois, est incomplète mais devrait vous aider à démarrer!
WITH base_sales AS
(
SELECTrn = ROW_NUMBER() OVER (ORDER BY mnth),
mnth = LEFT(mnth, 6), qty
FROM dbo.Sales
),
details AS
(
SELECT--[a.rn] = a.rn,
--[b.rn] = b.rn,
[a.mnth] = a.mnth,
[b.mnth] = b.mnth,
--[a.qty] = a.qty,
--[b.qty] = b.qty,
trend = CASE
WHEN a.rn = 1 THEN 'unknown'
WHEN b.qty > a.qty THEN 'UP'
WHEN b.qty = a.qty THEN 'SAME'
WHEN b.qty < a.qty THEN 'DOWN'
END
FROM base_sales a
CROSS APPLY base_sales b
WHERE a.rn = b.rn-1
)
SELECT *
FROM details;
-- Itzik Ben-Gan 2001
June 11, 2014 at 9:30 am
i got to a similar state of incompleteness. I thought I could apply a ranking function at this stage to get me to the next step, but i can't seem to manage it. I've got 2 pointer columns to identify the min and max dates for each band, but they aren't giving me the values I would expect. (Should be where values=1.) This rings a vague bell, about nuances in how the partitioning works, but I can't recall exactly the thing.
La mienne non plus n'est pas complète, mais si ca peut t'avancer... Il y a un petit problème avec les champs nommés MinDatePointer et MaxDatePointer qui sont censés etre égal à un quand c'est la date qui va bien....mais ca n'a pas l'air de bien fonctionner.
IF OBJECT_ID('tempdb.dbo.#sales') IS NOT NULL DROP TABLE #Sales;
GO
CREATE TABLE dbo.#Sales (mnth DATE primary key, qty int);
GO
INSERT INTO #Sales(mnth, qty) VALUES
('20071201', 100),
('20080101', 110),
('20080201', 120),
('20080301', 130),
('20080401', 140),
('20080501', 140),
('20080601', 130),
('20080701', 120),
('20080801', 110),
('20080901', 100),
('20081001', 110),
('20081101', 100),
('20081201', 120),
('20090101', 130),
('20090201', 140),
('20090301', 100),
('20090401', 100),
('20090501', 100),
('20090601', 110),
('20090701', 120),
('20090801', 110),
('20090901', 120),
('20091001', 130),
('20091101', 140),
('20091201', 100);
;WITH numberedrows AS
(
SELECT row_number() OVER (ORDER BY mnth) AS rownum,* FROM #Sales
), compare AS
(
SELECT cur.rownum,cur.mnth AS curmnth, cur.qty curqty, prev.mnth AS prevmnth, prev.qty AS prevqty FROM numberedrows cur LEFT JOIN numberedrows prev ON prev.rownum = cur.rownum-1
),
c2 AS
(
SELECT c.rownum,
c.curmnth
,c.curqty
,c.prevmnth
,c.prevqty, CASE
WHEN c.curqty > isnull(c.prevqty,c.curqty) THEN 'UP'
WHEN c.curqty < isnull(c.prevqty,c.curqty) THEN 'DOWN'
WHEN c.curqty = isnull(c.prevqty,c.curqty) THEN 'UNKNOWN'
END AS trend
FROM compare c
)
SELECT *, row_number() OVER (PARTITION BY trend ORDER BY c2.rownum) AS mindatepointer
,row_number() OVER (PARTITION BY trend ORDER BY c2.rownum desc) AS maxdatepointer
FROM c2 ORDER BY c2.rownum
June 11, 2014 at 10:48 am
Here is a solution entirely based on SQL 2012 window functions, I left it a little long winded to make it easier to follow.
😎
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.sales') IS NOT NULL DROP TABLE sales;
GO
CREATE TABLE dbo.Sales (mnth bigint primary key, qty int);
GO
INSERT INTO dbo.Sales(mnth, qty) VALUES
(20071201, 100),
(20080101, 110),
(20080201, 120),
(20080301, 130),
(20080401, 140),
(20080501, 140),
(20080601, 130),
(20080701, 120),
(20080801, 110),
(20080901, 100),
(20081001, 110),
(20081101, 100),
(20081201, 120),
(20090101, 130),
(20090201, 140),
(20090301, 100),
(20090401, 100),
(20090501, 100),
(20090601, 110),
(20090701, 120),
(20090801, 110),
(20090901, 120),
(20091001, 130),
(20091101, 140),
(20091201, 100);
GO
;WITH SALE_LIST AS
(
SELECT
SL.mnth
,SL.qty
,LAG(SL.qty,1,NULL) OVER
(
ORDER BY SL.mnth ASC
) AS LAST_qty
FROM dbo.Sales SL
)
,SALE_BASE AS
(
SELECT
SL.mnth
,ROW_NUMBER() OVER
(
ORDER BY SL.mnth
) AS MNTH_RID
,SL.qty
,ISNULL(SL.LAST_qty,SL.qty) AS LAST_qty
,CASE
WHEN SL.qty = SL.LAST_qty THEN 'SAME'
WHEN SL.qty > SL.LAST_qty THEN 'UP'
WHEN SL.qty < SL.LAST_qty THEN 'DOWN'
WHEN SL.LAST_qty IS NULL THEN 'unknown'
END AS TREND
FROM SALE_LIST SL
)
,SALE_GROUP AS
(
SELECT
SB.mnth
,SB.MNTH_RID
,SB.qty
,SB.TREND
,CASE
WHEN SB.TREND <> LAG(SB.TREND,1) OVER
(
ORDER BY SB.MNTH_RID
) THEN 1
ELSE 0
END AS GR_INCREMENT
FROM SALE_BASE SB
)
,SALE_SUMMARIZED AS
(
SELECT
SG.mnth
,SG.MNTH_RID
,SG.TREND
,SUM(SG.GR_INCREMENT) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY SG.MNTH_RID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GROUP_VALUE
FROM SALE_GROUP SG
)
,SALE_FINAL AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SS.GROUP_VALUE
ORDER BY SS.MNTH_RID
) AS GROUP_RID
,FIRST_VALUE(SS.mnth) OVER
(
PARTITION BY SS.GROUP_VALUE
ORDER BY SS.MNTH_RID
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS FIRST_MNTH
,LAST_VALUE(SS.mnth) OVER
(
PARTITION BY SS.GROUP_VALUE
ORDER BY SS.MNTH_RID
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS LAST_MNTH
,SS.TREND
FROM SALE_SUMMARIZED SS
)
SELECT
SF.FIRST_MNTHAS Start_Range
,SF.LAST_MNTHAS End_Range
,SF.TREND AS Trend
FROM SALE_FINAL SF
WHERE SF.GROUP_RID = 1
Results
Start_Range End_Range Trend
-------------------- -------------------- -------
20071201 20071201 unknown
20080101 20080401 UP
20080501 20080501 SAME
20080601 20080901 DOWN
20081001 20081001 UP
20081101 20081101 DOWN
20081201 20090201 UP
20090301 20090301 DOWN
20090401 20090501 SAME
20090601 20090701 UP
20090801 20090801 DOWN
20090901 20091101 UP
20091201 20091201 DOWN
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply