January 17, 2012 at 1:52 pm
L' Eomot Inversé (1/17/2012)
GSquared (1/17/2012)
I think this whole thing may be getting massively overcomplicated by the math.Is the actual business requirement simply a count of how many distinct datakeys there are, and a count of how many distinct landline-datakey pairs there are, or did some manager/executive somewhere actually request something this mathematically complicated?
What is the actual statement of the exact business need? As written/stated by the person who will actually be using this data, not summarized or anything like that.
I'm sure you are right, that the way it's been described to us is vastly overcomplicated. Now that Bicky has posted the code he's trying to replace we can maybe work out what it actually does and take that as a definite requirement - because the motivation for replacing it appears to be to get it to go faster, not to change the functionality.
On "by the math" I disagree with you: math is a way of providing clear and unequivocal specifications of requirements; one for of math is to present an algorithm (which Bicky has now done, and which Jared made an interesting attempt at, no doubt provoking that response from Bicky). What has complicated this has been the utter lack of clarity in the requirement (which Bicky's most recent post has probably fixed).
"Math" as a language can be very clear, and extremely precise. I think that's not the case here, because the math is being used to translate from unclear and incomplete English.
I'm not saying math is the problem, I'm saying all the math here is part of the problem, because it's premature, without a clear business-rule for the math to turn into something an engineer can implement.
It's like getting into stress-tollerance and hardness numbers for various alloys of steel which can be used in construction, when all we know is that a bridge is needed. If it's over a 1m wide creek that gets occassional foot traffic, mostly by kids, steel alloy analysis is overkill. If it's going to allow heavy freight to cross the Straights of Gibraltar, steel isn't going to come even close to being strong enough, no matter what alloy. We're still in the "someone needs a bridge" stage, without the details of what it's crossing and what it's intended to carry.
So we talk math, and shotgun various approaches, and hope it looks like what's needed on a small sample of data similar to the real data, but not the real data. We should be talking "over what and carrying what", not "how strong an alloy of steel".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2012 at 3:07 pm
bicky1980 (1/17/2012)
SQLKnowItAll (1/17/2012)
SQLKnowItAll (1/17/2012)
If the data could be processed in the following manner that would be great
1. Extract all the data with a landline
2. Dedupe the data on datakey - keep only one record (e.g. 3 duplicate datakeys would become just one datakey)
3. Dedupe the data on landline - same rule as above
This misses which record to keep in step 2. That is the big issue here... Which record (if any) get retained for comparison on landline from datakey? Clearly, that matters. I believe it is based on maximizing the count; i.e. if a data key only has 1 unique landline, do not count that landline in the determination of other datakey counts. Right?
EDIT: If the above is true, then a count of distinct landlines where landline meets the acceptable criteria for length is the same thing.
Ok, my edit was wrong because in the case where a datakey has 2 different landlines, but those 2 landlines do not appear on any other datakey... They still only count as 1. I think that is correct?
Your question, "in the case where a datakey has 2 different landlines, but those 2 landlines do not appear on any other datakey... They still only count as 1. I think that is correct?" - The answer is yes.
Just to try and simplify my request, this is how I have been told to preform these counts in the past: (Indkey is a unique key)
select *into [tablename1_landlines]
from [tablename1]
where landline is not null and landline!=''
Select max(indkey) as indkey,
max(datakey) as datakey
into [tablename1_landlines_temp]
from [tablename1_landlines]
group by datakey
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp])
Select max(indkey) as indkey,
max(landline) as landline
into [tablename1_landlines_temp2]
from [tablename1_landlines]
group by datakey
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp2])
I know this is certainly not the best way to do these and is very slow hence why I am trying to get a faster, better solution.
Unlikely though it may seem at first glance, this does generate correct results from the extended sample data set. Here's a functionally equivalent version which uses CTE's instead of "temp" tables which will probably work twice as fast (because the "temp tables" weren't indexed):
;WITH
-- datakeys distincted by max indkey
FirstInclusion AS (
Select max(indkey) as indkey --,
--datakey -- MAX not required
from #test2
group by datakey
),
-- pick landline with highest indkey from rows with same datakey
SecondInclusion AS (
Select max(indkey) as indkey --,
--max(landline) as landline
from #test2
where indkey in (select indkey from FirstInclusion)
group by datakey
)
SELECT *
FROM #test2
WHERE indkey IN (select indkey from FirstInclusion)
OR indkey IN (select indkey from SecondInclusion)
ORDER BY indkey
The results are quite similar to this:
;WITH Level1 AS (
SELECT ind, indkey, datakey, landline
FROM (
SELECT ind = 1, indkey, datakey, landline,
rnll = ROW_NUMBER() OVER(PARTITION BY landline ORDER BY datakey DESC),
rndk = ROW_NUMBER() OVER(PARTITION BY datakey ORDER BY landline DESC)
FROM #test2
WHERE landline <> ''
) d
WHERE rnll = 1 AND rndk = 1
),
Level2 AS (
SELECT ind, indkey, datakey, landline
FROM (
SELECT
ind = 2, indkey, datakey, landline,
rnll = ROW_NUMBER() OVER(PARTITION BY landline ORDER BY datakey DESC),
rndk = ROW_NUMBER() OVER(PARTITION BY datakey ORDER BY landline DESC)
FROM #test2 t
WHERE landline <> ''
AND NOT EXISTS (SELECT 1 FROM Level1 l WHERE l.landline = t.landline OR l.datakey = t.datakey)
) d
WHERE rnll = 1 AND rndk = 1
)
SELECT ind, indkey, datakey, landline
FROM (
SELECT ind, indkey, datakey, landline FROM Level1
UNION ALL
SELECT ind, indkey, datakey, landline FROM Level2
) d
ORDER BY indkey --datakey, landline
- the number of rows returned is the same, but there are some differences between sacrificed rows.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 17, 2012 at 3:22 pm
With a tiny mod, this produces the same results as Bicky's original query:
;WITH Level1 AS (
SELECT indkey, datakey, landline
FROM (
SELECT indkey, datakey, landline,
rnll = ROW_NUMBER() OVER(PARTITION BY landline ORDER BY indkey DESC),
rndk = ROW_NUMBER() OVER(PARTITION BY datakey ORDER BY indkey DESC)
FROM #test2
WHERE landline <> ''
) d
WHERE rnll = 1 AND rndk = 1
),
Level2 AS (
SELECT indkey, datakey, landline
FROM (
SELECT
indkey, datakey, landline,
rnll = ROW_NUMBER() OVER(PARTITION BY landline ORDER BY indkey DESC),
rndk = ROW_NUMBER() OVER(PARTITION BY datakey ORDER BY indkey DESC)
FROM #test2 t
WHERE landline <> ''
AND NOT EXISTS (SELECT 1 FROM Level1 l WHERE l.landline = t.landline OR l.datakey = t.datakey)
) d
WHERE rnll = 1 AND rndk = 1
)
SELECT indkey, datakey, landline FROM Level1
UNION ALL
SELECT indkey, datakey, landline FROM Level2
ORDER BY datakey, landline
... so the original query is not so daft as it looks.
Edit: sheesh, this is doing my head in. Bicky's original query is in fact functionally equivalent to this:
Select max(indkey) as indkey
from #landlines (#temp filtered for landlines)
group by datakey
-and works on the small datasets purely by accident. It will have dupes on landline in larger data sets. Here's a dataset from which it will return dupes on landline:
DROP TABLE #test2
CREATE TABLE #test2 (indkey NVARCHAR(2), datakey NVARCHAR(4), landline NVARCHAR(11), mobile NVARCHAR(11), email NVARCHAR(20))
INSERT INTO #test2 VALUES
('01','0001','01234567890','0712345679','1@test.co.uk') -- YES
,('02','0001','01234567890','','1@test.co.uk') -- x NO, dupe on indkey = 1 (datakey & landline)
,('03','0002','01234567890','','2@test.co.uk') -- NO, dupe on indkey = 1 (landline)
,('04','0002','01234567891','','2@test.co.uk') -- x YES
,('05','0002','','07123456789','')-- NO, dupe on indkey = 4 (datakey)
,('06','0003','01234567892','07123456791','') -- x YES
,('07','0004','01234567893','07123456792','') -- x YES
,('08','0005','01234567894','07123456793','2@test.co.uk') -- YES
,('09','0005','01234567898','07123456793','2@test.co.uk') -- x NO, dupe on indkey = 8 (datakey)
,('10','0008','01234567894','07123456793','9@test.co.uk') -- YES
,('11','0008','01234567895','','5@test.co.uk') -- NO, dupe on indkey = 10 (datakey)
,('12','0008','01234567898','','6@test.co.uk') -- x NO, dupe on indkey = 10 (datakey)
,('13','0009','01234567896','','6@test.co.uk') -- NO, dupe on indkey = 10 (landline)
,('14','0009','01234567889','','6@test.co.uk') -- x YES
,('15','0010','01189567889','','6@test.co.uk') -- YES
,('16','0010','01189567890','','6@test.co.uk') -- x NO, dupe on indkey = 15 (datakey)
,('17','0001','03189567889','','6@test.co.uk') -- NO, dupe on indkey = 1 (datakey)
,('18','0002','03189567890','','6@test.co.uk') -- NO, dupe on indkey = 4 (datakey)
,('19','0003','03189567891','','6@test.co.uk') -- NO, dupe on indkey = 6 (datakey)
Returns indkeys 9 and 12.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 17, 2012 at 4:25 pm
ChrisM@home (1/17/2012)
L' Eomot Inversé (1/17/2012)
Here's another attempt at making some progress towards a precise statement or the requirement....Tom, how does the query I posted here fit in with all this? Be nice, I majored in biochemistry/microbiology, not maths.
BTW Bicky's not necessarily a "he" - could be Rebecca from Australia 😉
Can I use the classic excuse that when in doubt one uses the masculine, which is supposed to embrace the feminine? :blush: Sometime I write "he" or something like that, but mostly I can't be bothered (for good and sensible reasons that some feminists agree with while others find them offensive).
Well, on the query I think it's a bit optimistic to think you will find enough rows where both rndk and rnll are 1 to get a complete result (same error as Jared's - too optimistic about reductions continuing to be available at every step); but counting every such row as a first step, and repeating that with what would be left after eliminating what the selection of those rows requires you to eliminate is certainly a reasonable way to begin, you just need to add code to cope with the case where this fails to choose more rows but uneliminated rows still exist. So if you put it in an iteration where for example if there are no rows with both rndk and rnll are 1 you instead select rows where rndk = 1 and rnll = min(rnll where rndk=1) (or swap the roles or rnll and rndk in that description, or do something else that is certain to pick at least one row) you end up with something that works (produces a result which conforms to what in one of my posts I called option P2 provided you iterate it - or recurse it - until everything is eliminated) so you definitely have the basis for a good solution.
Of course whether it is the right solution depends of what the requirement is, and the only way we have to work that out is to understand the code that Bicky posted as being the working but poorly performing version that is to be replaced. I haven't yet found time to work out just what that is, although it looks simple enough.
On the biochem and microbiology vs maths front, I think that as a basis for computing either is as good as the other; I have a particularly strong disrespect for CS and IT degrees from most universities, but believe that a degree in almost any other subject is an excellent preparation for a career in computing (or IT or DBAing or whatever anyone wants to call it). I tend to use math-based logic with concepts like sets and maxima and minima quite often to describe requirements and potential solutions, because I've found that salesmen, accountants, and general managers actually understand stuff presented like that a lot better better than they understand stuff presented in convoluted sentences designed to avoid "difficult" concepts like sets and maxima and minima.
Tom
January 17, 2012 at 5:03 pm
I think that we can all agree that this was not a mathematical issue but a logic issue. The two not being mutually exclusive, but being related. Anyway... I still have yet to know how this data is being interpreted and what the meaning of it is. So far, I cannot draw any conclusions or predictions based on that number with the way that it is being obtained. I of course operate under the assumption that we gather data statistics to make predictions or to come up with some conclusion. When the requests come from poorly educated decision makers (too many in the c-class), they make poor decisions because they don't understand what the numbers they are asking for really represent.
I consider it my duty when in a BA role to question the meaning of the data I am retrieving so that I can suggest an alternative if one is needed. Data like this should be answering a specific question. I really want to know what that question is before I pull data trying to answer it. Also don't wat my CEO to look like an idiot when he takes his data to the Board of Directors and tries to explain it incorrectly.
Jared
CE - Microsoft
January 18, 2012 at 8:01 am
SQLKnowItAll (1/17/2012)
I think that we can all agree that this was not a mathematical issue but a logic issue. The two not being mutually exclusive, but being related. Anyway... I still have yet to know how this data is being interpreted and what the meaning of it is. So far, I cannot draw any conclusions or predictions based on that number with the way that it is being obtained. I of course operate under the assumption that we gather data statistics to make predictions or to come up with some conclusion. When the requests come from poorly educated decision makers (too many in the c-class), they make poor decisions because they don't understand what the numbers they are asking for really represent.I consider it my duty when in a BA role to question the meaning of the data I am retrieving so that I can suggest an alternative if one is needed. Data like this should be answering a specific question. I really want to know what that question is before I pull data trying to answer it. Also don't wat my CEO to look like an idiot when he takes his data to the Board of Directors and tries to explain it incorrectly.
Do you require more info from me?
January 18, 2012 at 8:10 am
bicky1980 (1/18/2012)
SQLKnowItAll (1/17/2012)
I think that we can all agree that this was not a mathematical issue but a logic issue. The two not being mutually exclusive, but being related. Anyway... I still have yet to know how this data is being interpreted and what the meaning of it is. So far, I cannot draw any conclusions or predictions based on that number with the way that it is being obtained. I of course operate under the assumption that we gather data statistics to make predictions or to come up with some conclusion. When the requests come from poorly educated decision makers (too many in the c-class), they make poor decisions because they don't understand what the numbers they are asking for really represent.I consider it my duty when in a BA role to question the meaning of the data I am retrieving so that I can suggest an alternative if one is needed. Data like this should be answering a specific question. I really want to know what that question is before I pull data trying to answer it. Also don't wat my CEO to look like an idiot when he takes his data to the Board of Directors and tries to explain it incorrectly.
Do you require more info from me?
I think the others are well on their way to helping you with a single query to do this, but I have my own curiosities lying in what this data is supposed to tell you. So, if you know that, I would love to know what someone is interpreting this number as. Because you cannot say it is the total unique landlines, you cannot say it is the total number of unique datakey-landline pairs. So what does it describe? I understand if you do not know and you just want to get it done for your boss or whomever is asking for it. I am just inquisitive on this because of my nature and my love of interpreting statistics.
Jared
CE - Microsoft
January 18, 2012 at 8:33 am
bicky1980 (1/11/2012)
...These are just the basic counts, I will need to then run these incoporating the where clause for various variables. If someone could advise of the best way to run these, then at least thats a start. The way I am currently processing these is very long winded and I am sure there are much easier & better ways to run them...
bicky1980 (1/17/2012)
Just to try and simplify my request, this is how I have been told to preform these counts in the past: (Indkey is a unique key)
select *into [tablename1_landlines]
from [tablename1]
where landline is not null and landline!=''
Select max(indkey) as indkey,
max(datakey) as datakey
into [tablename1_landlines_temp]
from [tablename1_landlines]
group by datakey
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp])
Select max(indkey) as indkey,
max(landline) as landline
into [tablename1_landlines_temp2]
from [tablename1_landlines]
group by datakey
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp2])
I know this is certainly not the best way to do these and is very slow hence why I am trying to get a faster, better solution.
The "original query" above can be deconstructed as follows:
-- query 1
select *
into [tablename1_landlines]
from [tablename1]
where landline is not null and landline!=''
-- query 2
Select max(indkey) as indkey
into [tablename1_landlines_temp]
from [tablename1_landlines]
group by datakey
-- yields unique values of datakey with highest value of indkey per datakey
-- query 3
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp])
-- leaves only indkey/datakey values from query 2
-- query 4
Select max(indkey) as indkey
into [tablename1_landlines_temp2]
from [tablename1_landlines]
group by datakey
-- since only indkey/datakey values from query 2 remain in the table, and datakey is unique,
-- this returns exactly the same as query 2
-- query 5
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp2])
-- does nothing, because it's already been done by query 3
In answer to your opening post on this thread - yes, this can be done much more quickly, as follows (there are numerous ways of doing this):
SELECT somevalue = COUNT(*)
FROM [tablename1] t
INNER JOIN (
Select max(indkey) as indkey
from [tablename1]
where landline is not null and landline!=''
group by datakey
) d ON d.indkey = t.indkey
Looks to me like this is just the landline count. Can you confirm?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 8:40 am
SQLKnowItAll (1/18/2012)
bicky1980 (1/18/2012)
SQLKnowItAll (1/17/2012)
I think that we can all agree that this was not a mathematical issue but a logic issue. The two not being mutually exclusive, but being related. Anyway... I still have yet to know how this data is being interpreted and what the meaning of it is. So far, I cannot draw any conclusions or predictions based on that number with the way that it is being obtained. I of course operate under the assumption that we gather data statistics to make predictions or to come up with some conclusion. When the requests come from poorly educated decision makers (too many in the c-class), they make poor decisions because they don't understand what the numbers they are asking for really represent.I consider it my duty when in a BA role to question the meaning of the data I am retrieving so that I can suggest an alternative if one is needed. Data like this should be answering a specific question. I really want to know what that question is before I pull data trying to answer it. Also don't wat my CEO to look like an idiot when he takes his data to the Board of Directors and tries to explain it incorrectly.
Do you require more info from me?
I think the others are well on their way to helping you with a single query to do this, but I have my own curiosities lying in what this data is supposed to tell you. So, if you know that, I would love to know what someone is interpreting this number as. Because you cannot say it is the total unique landlines, you cannot say it is the total number of unique datakey-landline pairs. So what does it describe? I understand if you do not know and you just want to get it done for your boss or whomever is asking for it. I am just inquisitive on this because of my nature and my love of interpreting statistics.
The statistics are being used to provide the sale team with marketing counts to use to assist them in sales.
e.g. We have 10,125 people in Aberdeen with unique Landlines and @ one per Household
January 18, 2012 at 8:44 am
ChrisM@home (1/18/2012)
bicky1980 (1/11/2012)
...These are just the basic counts, I will need to then run these incoporating the where clause for various variables. If someone could advise of the best way to run these, then at least thats a start. The way I am currently processing these is very long winded and I am sure there are much easier & better ways to run them...bicky1980 (1/17/2012)
Just to try and simplify my request, this is how I have been told to preform these counts in the past: (Indkey is a unique key)
select *into [tablename1_landlines]
from [tablename1]
where landline is not null and landline!=''
Select max(indkey) as indkey,
max(datakey) as datakey
into [tablename1_landlines_temp]
from [tablename1_landlines]
group by datakey
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp])
Select max(indkey) as indkey,
max(landline) as landline
into [tablename1_landlines_temp2]
from [tablename1_landlines]
group by datakey
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp2])
I know this is certainly not the best way to do these and is very slow hence why I am trying to get a faster, better solution.
The "original query" above can be deconstructed as follows:
-- query 1
select *
into [tablename1_landlines]
from [tablename1]
where landline is not null and landline!=''
-- query 2
Select max(indkey) as indkey
into [tablename1_landlines_temp]
from [tablename1_landlines]
group by datakey
-- yields unique values of datakey with highest value of indkey per datakey
-- query 3
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp])
-- leaves only indkey/datakey values from query 2
-- query 4
Select max(indkey) as indkey
into [tablename1_landlines_temp2]
from [tablename1_landlines]
group by datakey
-- since only indkey/datakey values from query 2 remain in the table, and datakey is unique,
-- this returns exactly the same as query 2
-- query 5
delete from [tablename1_landlines]
where indkey not in (select indkey from [tablename1_landlines_temp2])
-- does nothing, because it's already been done by query 3
In answer to your opening post on this thread - yes, this can be done much more quickly, as follows (there are numerous ways of doing this):
SELECT somevalue = COUNT(*)FROM [tablename1] t
INNER JOIN (
Select max(indkey) as indkey
from [tablename1]
where landline is not null and landline!=''
group by datakey
) d ON d.indkey = t.indkey
Looks to me like this is just the landline count. Can you confirm?
Yes this would cover just the landline count, so using the same rules as landlines would need figures for emails & mobiles as well.
January 18, 2012 at 8:46 am
All yours Gus.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 19, 2012 at 3:44 am
Who's Gus?
January 19, 2012 at 6:15 am
bicky1980 (1/19/2012)
Who's Gus?
Me
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2012 at 6:31 am
I see what the original method is doing. The way it goes about achieving it isn't particularly sensible, but it does have a functional pattern to it.
Is the query run repeatedly, or just once?
On the numbers that the salespeople are citing, does a datakey correspond to a household, or to a location? I.e.: We have X households in Y city, does datakey correspond to X or Y?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2012 at 6:34 am
GSquared (1/19/2012)
I see what the original method is doing. The way it goes about achieving it isn't particularly sensible, but it does have a functional pattern to it.Is the query run repeatedly, or just once?
On the numbers that the salespeople are citing, does a datakey correspond to a household, or to a location? I.e.: We have X households in Y city, does datakey correspond to X or Y?
Hi Gus 😉
Datakey is a unique household identifier, so it would correspond to X
Thanks
Viewing 15 posts - 106 through 120 (of 120 total)
You must be logged in to reply to this topic. Login to reply