June 19, 2014 at 4:17 pm
hello SSC's Friends
Today i was doing a query for store procedure and one of the query was this, i hope an enthusiastic guy could do it 😀
Alright this is it:
I got this data
M-Is male or not
F-Is female or not
Age
C-Category code
M F Age C
1036A
1036A
1036A
1028A
100BB
100BB
1026A
10113P
010BB
1024A
1035A
0176A
107NI
1022A
10113P
1024A
1035A
1024A
1024A
107NI
1054A
100BB
010BB
1054A
010BB
1031A
0136A
0123A
104NI
0156A
1052A
10113P
0137A
10114P
101BB
016NI
0139A
100BB
1044A
010BB
1051A
1036A
100BB
1035A
1032A
0128A
010BB
1045A
1044A
1031A
1022A
1035A
0136A
010BB
1033A
1033A
101BB
1031A
1033A
0147A
1038A
105NI
010BB
010BB
1024A
1028A
0129A
1032A
0136A
0123A
101BB
1032A
1036A
104NI
1032A
1024A
1026A
1032A
1035A
1033A
1024A
105NI
010BB
105NI
010BB
1044A
1044A
1032A
101BB
010BB
1044A
100BB
010BB
1024A
1028A
1032A
1048A
The expected result set is this:
C-Category code
NM-Number of male
NF-Number of female
L-literal, it doesnt matter what is the value of this.
C NM NF L
A5112A
BB1012A
NI71A
P40A
so on, can any one send me a good query with nice performance rather than the one i did 😛
BTW, i attached the script to populate the tbl_test for this.
Enjoy it.
June 19, 2014 at 4:21 pm
I forget something, is there any way to use windows functions on this example?
thanks 😀
June 19, 2014 at 4:51 pm
jaimepc199 (6/19/2014)
I forget something, is there any way to use windows functions on this example?thanks 😀
I am sure there is, but why complicate things when it is not necessary?
select codcategory C
, SUM(case when male = 1 then 1 else 0 end) NM
, SUM(case when female = 1 then 1 else 0 end) NF
, 'A' L
from tbl_test
group by codcategory
As far as performance goes, I have no idea how this will do compared to what you have done since you did not post your original query!
Also, performance will be greatly influenced by your indexes, keys and constraints. It is very difficult to say how fast it will run.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 19, 2014 at 5:16 pm
Nice one!
Indeed i guess that windows function would be better but like you say, usually we create a hyper mega cannon based on electron lasser to destroy an ant :w00t:
So on, this is the query i did in the begin:
Select
'C'=codcategory,
'NM'=(Select Count(c.Male) From tbl_test As c Where c.codcategory=t.codcategory And c.Male=1),
'NF'=(Select Count(c.Female) From tbl_test As c Where c.codcategory=t.codcategory And c.Female=1),
'L'='A'
From tbl_test as t
Group by codcategory
I guess yours is simply
June 19, 2014 at 5:51 pm
There's no need for window functions, they won't work better than a simple aggregate function.
You don't need to write a complete CASE either. Here are two options to shorten the code assuming that your Male and Female columns are bit. If they're int (or any other numeric), you could just use SUM directly.
SELECT codcategory AS C,
SUM( SIGN(Male)) AS NM,
SUM( SIGN(Female)) AS NF,
'A' AS L
FROM tbl_test
GROUP BY codcategory;
SELECT codcategory AS C,
COUNT( NULLIF(Male,0)) AS NM,
COUNT( NULLIF(Female,0)) AS NF,
'A' AS L
FROM tbl_test
GROUP BY codcategory;
By the way, you should facilitate things by giving DDL and sample data like this:
CREATE TABLE tbl_test(
Male bit,
Female bit,
Age int,
codcategory char(2))
INSERT INTO tbl_test VALUES
(1,0,36,'A'),
(1,0,36,'A'),
(1,0,36,'A'),
(1,0,28,'A'),
(1,0,0,'BB'),
(1,0,0,'BB'),
(1,0,26,'A'),
(1,0,113,'P'),
(0,1,0,'BB'),
(1,0,24,'A'),
(1,0,35,'A'),
(0,1,76,'A'),
(1,0,7,'NI'),
(1,0,22,'A'),
(1,0,113,'P'),
(1,0,24,'A'),
(1,0,35,'A'),
(1,0,24,'A'),
(1,0,24,'A'),
(1,0,7,'NI'),
(1,0,54,'A'),
(1,0,0,'BB'),
(0,1,0,'BB'),
(1,0,54,'A'),
(0,1,0,'BB'),
(1,0,31,'A'),
(0,1,36,'A'),
(0,1,23,'A'),
(1,0,4,'NI'),
(0,1,56,'A'),
(1,0,52,'A'),
(1,0,113,'P'),
(0,1,37,'A'),
(1,0,114,'P'),
(1,0,1,'BB'),
(0,1,6,'NI'),
(0,1,39,'A'),
(1,0,0,'BB'),
(1,0,44,'A'),
(0,1,0,'BB'),
(1,0,51,'A'),
(1,0,36,'A'),
(1,0,0,'BB'),
(1,0,35,'A'),
(1,0,32,'A'),
(0,1,28,'A'),
(0,1,0,'BB'),
(1,0,45,'A'),
(1,0,44,'A'),
(1,0,31,'A'),
(1,0,22,'A'),
(1,0,35,'A'),
(0,1,36,'A'),
(0,1,0,'BB'),
(1,0,33,'A'),
(1,0,33,'A'),
(1,0,1,'BB'),
(1,0,31,'A'),
(1,0,33,'A'),
(0,1,47,'A'),
(1,0,38,'A'),
(1,0,5,'NI'),
(0,1,0,'BB'),
(0,1,0,'BB'),
(1,0,24,'A'),
(1,0,28,'A'),
(0,1,29,'A'),
(1,0,32,'A'),
(0,1,36,'A'),
(0,1,23,'A'),
(1,0,1,'BB'),
(1,0,32,'A'),
(1,0,36,'A'),
(1,0,4,'NI'),
(1,0,32,'A'),
(1,0,24,'A'),
(1,0,26,'A'),
(1,0,32,'A'),
(1,0,35,'A'),
(1,0,33,'A'),
(1,0,24,'A'),
(1,0,5,'NI'),
(0,1,0,'BB'),
(1,0,5,'NI'),
(0,1,0,'BB'),
(1,0,44,'A'),
(1,0,44,'A'),
(1,0,32,'A'),
(1,0,1,'BB'),
(0,1,0,'BB'),
(1,0,44,'A'),
(1,0,0,'BB'),
(0,1,0,'BB'),
(1,0,24,'A'),
(1,0,28,'A'),
(1,0,32,'A'),
(1,0,48,'A');
EDIT:
I missed the file. I'm used to have the code in the post. :hehe:
June 19, 2014 at 11:24 pm
jaimepc199 (6/19/2014)
I forget something, is there any way to use windows functions on this example?thanks 😀
As the set has only one divisor (codcategory) and there is no requirement to preserve the details, there is only one "window" used. Therefore there is no gain, only additional cost by using the window functions. Typically there would be a single table/covering index scan in both cases but the difference would be additional stream aggregation, scalar computation and nested loop for each computed column (NM,NF).
The worst thing would be to combine the two, the cost would be the combined cost of both solutions minus one table scan. You can test this by running the code below with "show actual execution plan" set on.
😎
USE tempdb;
GO
/* GROUP BY */
SELECT
TT.codcategory AS C
,SUM(CAST(TT.male AS INT)) AS NM
,SUM(CAST(TT.Female AS INT)) AS NF
,'A' AS L
FROM dbo.tbl_test TT
GROUP BY TT.codcategory;
/* Window Function */
;WITH DATA_BASE AS
(
SELECT
TT.codcategory AS C
,ROW_NUMBER() OVER
(
PARTITION BY TT.codcategory
/* This order clause does not create any work
as there is already a Sort operator in the
execution plan for the partitioning.
Using (SELECT NULL) does not change the plan.
*/
ORDER BY TT.codcategory
) AS C_RID
,SUM(CAST(TT.male AS INT)) OVER
(
PARTITION BY TT.codcategory
) AS NM
,SUM(CAST(TT.Female AS INT)) OVER
(
PARTITION BY TT.codcategory
) AS NF
,'A' AS L
FROM dbo.tbl_test TT
)
SELECT
DB.C
,DB.NM
,DB.NF
,DB.L
FROM DATA_BASE DB
WHERE DB.C_RID = 1;
/* Combined */
;WITH DATA_BASE AS
(
SELECT
TT.codcategory AS C
,SUM(CAST(TT.male AS INT)) OVER
(
PARTITION BY TT.codcategory
) AS NM
,SUM(CAST(TT.Female AS INT)) OVER
(
PARTITION BY TT.codcategory
) AS NF
,'A' AS L
FROM dbo.tbl_test TT
)
SELECT
DB.C
,DB.NM
,DB.NF
,DB.L
FROM DATA_BASE DB
GROUP BY DB.C,DB.NM,DB.NF,DB.L;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply