May 20, 2012 at 10:49 pm
Yes, I did.
Plz see key1=002 and 003 with rank_within_key_product.
key1 date product_id rank_within_key_and_product rank_within_key counting
001 20100403 004 1 1 1
001 20100505 003 1 2 2
001 20100615 002 1 3 3
001 20100716 001 1 4 4
001 20110813 001 2 5 4
001 20110923 001 3 6 4
002 20100403 002 1 1 1
002 20100612 002 2 2 1
002 20110503 004 1 3 3 (should be 2)
002 20120103 005 1 4 4 (should be 3)
003 20100612 002 1 1 1
003 20100831 005 1 2 2
003 20110214 005 2 3 2
003 20110603 005 3 4 2
003 20120803 001 1 5 5 (should be 3)
May 20, 2012 at 11:49 pm
CREATE TABLE table2
(
key1 char(3),
[date] date,
product_id char(3),
value1 integer,
);
INSERT INTO dbo.table2 VALUES ('001','20100505','003',300);
INSERT INTO dbo.table2 VALUES ('001','20100615','002',200);
INSERT INTO dbo.table2 VALUES ('001','20100716','001',100);
INSERT INTO dbo.table2 VALUES ('001','20110813','001',100);
INSERT INTO dbo.table2 VALUES ('001','20110923','001',100);
INSERT INTO dbo.table2 VALUES ('001','20100403','004',400);
INSERT INTO dbo.table2 VALUES ('002','20100612','002',400);
INSERT INTO dbo.table2 VALUES ('002','20110503','004',500);
INSERT INTO dbo.table2 VALUES ('002','20120103','005',200);
INSERT INTO dbo.table2 VALUES ('002','20100403','002',200);
INSERT INTO dbo.table2 VALUES ('003','20100612','002',300);
INSERT INTO dbo.table2 VALUES ('003','20100831','005',500);
INSERT INTO dbo.table2 VALUES ('003','20110214','005',500);
INSERT INTO dbo.table2 VALUES ('003','20110603','005',500);
INSERT INTO dbo.table2 VALUES ('003','20120803','001',100);
CREATE UNIQUE INDEX uq1 ON dbo.table2 (key1, [date]) INCLUDE (product_id);
-- SQL Server 2012 only
SELECT
key1,
product_id,
product_sw =
1 + COUNT_BIG(changed) OVER (
PARTITION BY key1
ORDER BY [date])
FROM
(
SELECT
t.key1,
t.product_id,
t.[date],
CASE
WHEN product_id !=
LAG(product_id) OVER (
PARTITION BY key1
ORDER BY [date])
THEN 1
END AS changed
FROM dbo.table2 AS t
) AS sq1
ORDER BY
key1,
[date];
-- SQL Server 2005+
SELECT
t.key1,
t.product_id,
t.[date],
changed = ISNULL(prev.changed, 1)
INTO #t
FROM table2 AS t
OUTER APPLY
(
SELECT TOP (1)
changed = CASE WHEN t2.product_id = t.product_id THEN 0 ELSE 1 END
FROM table2 AS t2
WHERE
t2.key1 = t.key1
AND t2.[date] < t.[date]
ORDER BY
t2.[date] DESC
) AS prev;
CREATE UNIQUE CLUSTERED INDEX c ON #t (key1, [date]);
SELECT
t.key1,
t.product_id,
t.[date],
product_sw =
(
SELECT
SUM(t2.changed)
FROM #t AS t2
WHERE
t2.key1 = t.key1
AND t2.[date] <= t.[date]
)
FROM #t AS t
ORDER BY
t.key1,
t.[date];
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 21, 2012 at 12:32 am
I think it worked for him...I asked him to post the performance matrxi for his table.
select t2.*,dtb.cnt from
table2 t2
inner join (
select *,row_number() over(partition by key1 order by date) as cnt
from (
select key1, date, product_id, row_number() over (partition by key1 ,product_id order by date) as rank_within_key_and_product
from table2
) dta where rank_within_key_and_product = 1
) dtb
on t2.key1= dtb.key1
and t2.product_id = dtb.product_id
order by key1, date
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 21, 2012 at 12:48 am
Gullimeel (5/21/2012)
I think it worked for him...I asked him to post the performance matrxi for his table.
Ah, I wish it had been clearer that the partitions were ordered by date.
Of course you'll only get the right sorting if the column is changed to a date type rather than the varchar(16) as given :rolleyes:
-- Updated solution for SQL 2012 only
SELECT
key1,
product_id,
product_sw =
1 + COUNT_BIG(changed) OVER (
PARTITION BY key1
ORDER BY [date])
FROM
(
SELECT
t.key1,
t.product_id,
t.[date],
CASE
WHEN product_id !=
LAG(product_id) OVER (
PARTITION BY key1
ORDER BY [date])
THEN 1
END AS changed
FROM dbo.table2 AS t
) AS sq1
ORDER BY
key1,
[date];
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2012 at 3:01 am
Hi, Gullimeel and SQL Kiwi,
Thank you very much! The SQLs posted work perfect.
There are 20TB DBs in total to make switching matrix with SQL2008R2 for Markov chain analysis with OR methods.
I cannot send performance data because security policy at my company. Sorry.
Anyway, I really appreciate your work!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply