May 14, 2012 at 6:42 am
I have tried to use Row_Partition and Dense_Rank to get ‘switching number with counting.’
SQL is quite tough....SPSS/SAS has transpose function. But, SQL does not.
Sample data:
declare @tbl table (id int, product char(1))
insert into @tbl (id, product) values
(1, 'a'),
(1, 'a'),
(1, 'b'),
(2, 'c'),
(2, 'c'),
(2, 'd'),
(2, 'c'),
(2, 'd'),
(2, 'd'),
(3, 'e'),
(3, 'e'),
(3, 'e'),
(3, 'd'),
(3, 'd'),
(3, 'f'),
(3, 'f'),
(3, 'g')
I want to get switching number with counting like:
1, a, 1
1, a, 1
1, b, 2
2, c, 1
2, c, 1
2, d, 2
2, c, 3
2, d, 4
2, d, 4
3, e, 1
3, e, 1
3, e, 1
3, d, 2
3, d, 2
3, f, 3
3, f, 3
3, g, 4
Switching Condition:
If product <> lag (product) and id =lag(id), count=count+1
If id<>=lag(id), count=1
Thanks in advance.
May 14, 2012 at 6:56 am
that's dense_rank.
select *, dense_rank() over (partition by id order by product) dr
from @tbl
If you need for id of 3 get rank 1 for 'e' and rank 2 for 'd', you will need to do custom ordering which will make 'e' come before 'd'
May 14, 2012 at 6:59 am
Actually, I can see now what you want...
But then you will need to provide a bit more of data. There is no way that SQL will guarantee that inserting in the order eg.
(2, 'c'),
(2, 'c'),
(2, 'd'),
(2, 'c'),
(2, 'd')
will be selected back from the table in the same order.
Do you have any other column which shows the records order?
May 14, 2012 at 8:27 am
Thanks, Eugene Elutin.
I just did run your sql in my original pos data. It has same result of SAS!
It works fine!!
May 14, 2012 at 7:47 pm
athens1234 (5/14/2012)
Thanks, Eugene Elutin.I just did run your sql in my original pos data. It has same result of SAS!
It works fine!!
Only while you're looking. There is no guarantee of order in a SELECT unless you have an ORDER BY.
Also, just a hint... if you post your test data so that people who are working in SQL Server 2005 during the day (like me) can load the data, you'll get a lot more help. Using SQL Server 2008 only code cuts the number of people who can help just about in half.
--Jeff Moden
May 15, 2012 at 3:06 am
...
Only while you're looking. There is no guarantee of order in a SELECT unless you have an ORDER BY.
...
It's not applicable for dense_rank, where order is enforced. After second glance, I also thought that he needs a "running total" where the order would need to be enforced, but looks like he was mistaken with his data sample. We might never know for sure...
May 15, 2012 at 7:49 pm
I checked the dense_tank sql with other sample. I am wrong and stacked
Sample data:
create table table2
(
key1 varchar(16),
date varchar(16),
product_id varchar(16),
value1 integer
);
insert into table2 values ('001','20100505','003',300);
insert into table2 values ('001','20100615','002',200);
insert into table2 values ('001','20100716','001',100);
insert into table2 values ('001','20110813','001',100);
insert into table2 values ('001','20110923','001',100);
insert into table2 values ('001','20100403','004',400);
insert into table2 values ('002','20100612','004',400);
insert into table2 values ('002','20110503','005',500);
insert into table2 values ('002','20120103','002',200);
insert into table2 values ('002','20100403','002',200);
insert into table2 values ('003','20100612','003',300);
insert into table2 values ('003','20100831','005',500);
insert into table2 values ('003','20110214','005',500);
insert into table2 values ('003','20110603','005',500);
insert into table2 values ('003','20120803','001',100);
Run the SQL below.
select key1, date, product_id, dense_rank() over (partition by key1 order by product_id, date) as product_id_sw
from table2 order by key1, date;
Result:
key1 date product_id product_id_sw
---------------- ---------------- ---------------- --------------------
001 20100403 004 6
001 20100505 003 5
001 20100615 002 4
001 20100716 001 1
001 20110813 001 2
001 20110923 001 3
002 20100403 002 1
002 20100612 004 3
002 20110503 005 4
002 20120103 002 2
003 20100612 003 2
003 20100831 005 3
003 20110214 005 4
003 20110603 005 5
003 20120803 001 1
The result is wrong. I am stacked
I want to get switching numbers with counting like:
key1 date product_id product_id_sw
---------------- ---------------- ---------------- --------------------
001 20100403 004 1
001 20100505 003 2
001 20100615 002 3
001 20100716 001 4
001 20110813 001 4
001 20110923 001 4
002 20100403 002 1
002 20100612 004 2
002 20110503 005 3
002 20120103 002 4
003 20100612 003 1
003 20100831 005 2
003 20110214 005 2
003 20110603 005 2
003 20120803 001 3
I am looking forward to see hints.
Thanks in advance.
May 16, 2012 at 1:40 am
It can be done using "Quirky Update" method.
Read this great article by J. Moden: http://qa.sqlservercentral.com/articles/T-SQL/68467/
May 18, 2012 at 12:57 am
Hello Eugene,
I tried some scripts from the article you listed.
But, it did not work and is very tough to understand Quirky Update.
Please help me to write correct scripts.
Sample Data:
create table table2
(
key1 varchar(16),
date varchar(16),
product_id varchar(16),
value1 integer,
product_count integer
);
insert into table2 values ('001','20100505','003',300,NULL);
insert into table2 values ('001','20100615','002',200,NULL);
insert into table2 values ('001','20100716','001',100,NULL);
insert into table2 values ('001','20110813','001',100,NULL);
insert into table2 values ('001','20110923','001',100,NULL);
insert into table2 values ('001','20100403','004',400,NULL);
insert into table2 values ('002','20100612','004',400,NULL);
insert into table2 values ('002','20110503','005',500,NULL);
insert into table2 values ('002','20120103','002',200,NULL);
insert into table2 values ('002','20100403','002',200,NULL);
insert into table2 values ('003','20100612','003',300,NULL);
insert into table2 values ('003','20100831','005',500,NULL);
insert into table2 values ('003','20110214','005',500,NULL);
insert into table2 values ('003','20110603','005',500,NULL);
insert into table2 values ('003','20120803','001',100,NULL);
The script I modified
--===== Declare the working variables
DECLARE @Prevkey1 INT
DECLARE @Product_count INT
Declare @prev_product_id varchar(16)
--===== Update the running total and running count for this row using the "Quirky
-- Update" and a "Pseudo-cursor". The order of the UPDATE is controlled by the
-- order of the clustered index.
update dbo.table2
SET @prev_product_id = product_id = CASE
WHEN product_id<>@prev_product_id and key1=@Prevkey1
THEN @product_count+1
ELSE product_id
END,
@prev_product_id = product_id
FROM dbo.table2 WITH (TABLOCKX)
OPTION (MAXDOP 1)
GO
I want to see the table like below
key1 date product_id product_count
---------------- ---------------- ---------------- -------------
001 20100505 003 1
001 20100615 002 2
001 20100716 001 3
001 20110813 001 3
001 20110923 001 3
001 20100403 004 4
002 20100612 004 1
002 20110503 005 2
002 20120103 002 3
002 20100403 002 3
003 20100612 003 1
003 20100831 005 2
003 20110214 005 2
003 20110603 005 2
003 20120803 001 3
Thanks in advance!
May 18, 2012 at 2:47 am
select key1, date, product_id, dense_rank() over (partition by key1 order by product_id, date) as product_id_sw
from table2 order by key1, date;
take out the date from the order by in over. That is causing the issue.
Try below,
select key1, date, product_id, dense_rank() over (partition by key1 order by product_id) as product_id_sw
from table2 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 18, 2012 at 3:06 am
You can use two dense_rank here..
select *,rank_within_key - rank_within_key_and_product + 1 as counting from (
select key1, date, product_id, dense_rank() over (partition by key1 ,product_id order by date) as rank_within_key_and_product,dense_rank() over (partition by key1 order by date) as rank_within_key
from table2
) dta
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 18, 2012 at 3:07 am
You again provided insufficient sample of data, so people will advise you to use dens_rank and it's not what you need. If you could confirm the following:
001 20100403 004 1
001 20100505 003 2
001 20100615 002 3
001 20100716 001 4
001 20110813 001 4
001 20110923 001 4
001 20111020 002 5 (Should it be 5 or 3 here?, if 3, then you can use dense_rank, if 5 - then quirky update)
I'm not sure if I have time today for more help, however there are a lot people here who can help you with quirky update logic...
May 18, 2012 at 4:18 am
You can use two dense_rank here..
select *,rank_within_key - rank_within_key_and_product + 1 as counting from (
select key1, date, product_id, dense_rank() over (partition by key1 ,product_id order by date) as rank_within_key_and_product,dense_rank() over (partition by key1 order by date) as rank_within_key
from table2
) dta
order by key1, date
You can use row_number instead of dense_rank.
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 20, 2012 at 10:26 pm
Hi, Gullimeel.
I did run your
select key1, date, product_id, dense_rank() over (partition by key1 order by product_id) as product_id_sw
from table2 order by key1, date;
Using 'dense_rank'does not count 'switching' product_id.
Here is a sample of the switching table I want to see.
key1 product_id product_sw
001 003 1 (start as 1 count)
001 002 2 (003 is not previous 002: Count=Count+1)
001 001 3 (001 is not previous 002: Count=Count+1)
001 001 3 (001 is same as previous: Count=Count+0)
001 001 3 (001 is same as previous: Count=Count+0)
001 004 4 (004 is not previous 001: Count=Count+1)
002 002 1 (start as 1 count because key1 is new id)
002 004 2 (004 is not previous 004: Count=Count+1)
002 005 3 (005 is not previous 004: Count=Count+1)
002 002 4 (002 is not previous 005: Count=Count+1)
003 002 1 (start as 1 count becasue key1 is new id)
003 005 2 (005 is not previous 003: Count=Count+1)
003 005 2 (005 is same as 005: Count=Count+0)
003 005 2 (005 is same as 005: Count=Count+0)
003 001 3 (001 is not previos 005: Count=Count+1)
Sample Data:
create table table2
(
key1 varchar(16),
date varchar(16),
product_id varchar(16),
value1 integer,
product_count integer
);
insert into table2 values ('001','20100505','003',300,NULL);
insert into table2 values ('001','20100615','002',200,NULL);
insert into table2 values ('001','20100716','001',100,NULL);
insert into table2 values ('001','20110813','001',100,NULL);
insert into table2 values ('001','20110923','001',100,NULL);
insert into table2 values ('001','20100403','004',400,NULL);
insert into table2 values ('002','20100612','002',400,NULL);
insert into table2 values ('002','20110503','004',500,NULL);
insert into table2 values ('002','20120103','005',200,NULL);
insert into table2 values ('002','20100403','002',200,NULL);
insert into table2 values ('003','20100612','002',300,NULL);
insert into table2 values ('003','20100831','005',500,NULL);
insert into table2 values ('003','20110214','005',500,NULL);
insert into table2 values ('003','20110603','005',500,NULL);
insert into table2 values ('003','20120803','001',100,NULL);
Thanks in advance
May 20, 2012 at 10:39 pm
Did you try this?
select *,rank_within_key - rank_within_key_and_product + 1 as counting from (select key1, date, product_id, dense_rank() over (partition by key1 ,product_id order by date) as rank_within_key_and_product,dense_rank() over (partition by key1 order by date) as rank_within_keyfrom table2 ) dtaorder 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]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply