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
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
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
A 51 12 A
BB 10 12 A
NI 7 1 A
P 4 0 A
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