Ik heb een query op een groot aantal grote tabellen (rijen en kolommen) met een aantal joins, maar een van de tabellen heeft enkele dubbele rijen met gegevens die problemen veroorzaken voor mijn query. Aangezien dit een alleen-lezen realtime-feed is van een andere afdeling, kan ik die gegevens niet corrigeren, maar ik probeer problemen in mijn zoekopdracht te voorkomen.
Daarom moet ik deze waardeloze data als een left join toevoegen aan mijn goede query. De dataset ziet er als volgt uit:
IDNo FirstName LastName ...
-------------------------------------------
uqx bob smith
abc john willis
ABC john willis
aBc john willis
WTF jeff bridges
sss bill doe
ere sally abby
wtf jeff bridges
...
(ongeveer 2 dozijn kolommen en 100.000 rijen)
Mijn eerste instinct was om een duidelijk verschil uit te voeren, wat me ongeveer 80K rijen opleverde:
SELECT DISTINCT P.IDNo
FROM people P
Maar als ik het volgende probeer, krijg ik alle rijen terug:
SELECT DISTINCT P.*
FROM people P
OF
SELECT
DISTINCT(P.IDNo) AS IDNoUnq
,P.FirstName
,P.LastName
...etc.
FROM people P
Ik dacht toen dat ik een FIRST() aggregatiefunctie zou doen op alle kolommen, maar dat voelt ook verkeerd. Syntactisch doe ik hier iets verkeerd?
Bijwerken:
Ik wilde alleen opmerken: deze records zijn duplicaten op basis van een niet-sleutel / niet-geïndexeerd veld van ID dat hierboven wordt vermeld. De ID is een tekstveld dat, hoewel het dezelfde waarde heeft, een ander geval is dan de andere gegevens die het probleem veroorzaken.
Antwoord 1, autoriteit 100%
distinctis geeneen functie. Het werkt altijd op allekolommen van de geselecteerde lijst.
Uw probleem is een typisch “grootste N per groep”-probleem dat eenvoudig kan worden opgelost met een vensterfunctie:
select ...
from (
select IDNo,
FirstName,
LastName,
....,
row_number() over (partition by lower(idno) order by firstname) as rn
from people
) t
where rn = 1;
Met behulp van de order by-clausule kun je selecteren welke van de duplicaten je wilt kiezen.
Het bovenstaande kan worden gebruikt in een left join, zie hieronder:
select ...
from x
left join (
select IDNo,
FirstName,
LastName,
....,
row_number() over (partition by lower(idno) order by firstname) as rn
from people
) p on p.idno = x.idno and p.rn = 1
where ...
Antwoord 2, autoriteit 9%
Voeg een identiteitskolom (PeopleID) toe en gebruik vervolgens een gecorreleerde subquery om de eerste waarde voor elke waarde te retourneren.
SELECT *
FROM People p
WHERE PeopleID = (
SELECT MIN(PeopleID)
FROM People
WHERE IDNo = p.IDNo
)
Antwoord 3, autoriteit 4%
Het bleek dat ik het verkeerd deed. Ik moest eerst een geneste selectie uitvoeren van alleen de belangrijke kolommen, en een duidelijke selectie uitvoeren om te voorkomen dat prullenbakkolommen met ‘unieke’ gegevens mijn goede gegevens zouden beschadigen. Het volgende lijkt het probleem te hebben opgelost… maar ik zal de volledige dataset later proberen.
SELECT DISTINCT P2.*
FROM (
SELECT
IDNo
, FirstName
, LastName
FROM people P
) P2
Hier zijn wat afspeelgegevens zoals gevraagd: http://sqlfiddle.com/#!3/ 050e0d/3
CREATE TABLE people
(
[entry] int
, [IDNo] varchar(3)
, [FirstName] varchar(5)
, [LastName] varchar(7)
);
INSERT INTO people
(entry,[IDNo], [FirstName], [LastName])
VALUES
(1,'uqx', 'bob', 'smith'),
(2,'abc', 'john', 'willis'),
(3,'ABC', 'john', 'willis'),
(4,'aBc', 'john', 'willis'),
(5,'WTF', 'jeff', 'bridges'),
(6,'Sss', 'bill', 'doe'),
(7,'sSs', 'bill', 'doe'),
(8,'ssS', 'bill', 'doe'),
(9,'ere', 'sally', 'abby'),
(10,'wtf', 'jeff', 'bridges')
;
Antwoord 4, autoriteit 4%
Na zorgvuldige afweging heeft dit dillema een paar verschillende oplossingen:
Alles samenvoegen
Gebruik een aggregaat voor elke kolom om de grootste of kleinste veldwaarde te krijgen. Dit is wat ik doe omdat er 2 gedeeltelijk ingevulde records nodig zijn en de gegevens worden “samengevoegd”.
http://sqlfiddle.com/#!3/59cde/1
SELECT
UPPER(IDNo) AS user_id
, MAX(FirstName) AS name_first
, MAX(LastName) AS name_last
, MAX(entry) AS row_num
FROM people P
GROUP BY
IDNo
Eerste (of laatste record) halen
http://sqlfiddle.com/#!3/59cde/23
-- ------------------------------------------------------
-- Notes
-- entry: Auto-Number primary key some sort of unique PK is required for this method
-- IDNo: Should be primary key in feed, but is not, we are making an upper case version
-- This gets the first entry to get last entry, change MIN() to MAX()
-- ------------------------------------------------------
SELECT
PC.user_id
,PData.FirstName
,PData.LastName
,PData.entry
FROM (
SELECT
P2.user_id
,MIN(P2.entry) AS rownum
FROM (
SELECT
UPPER(P.IDNo) AS user_id
, P.entry
FROM people P
) AS P2
GROUP BY
P2.user_id
) AS PC
LEFT JOIN people PData
ON PData.entry = PC.rownum
ORDER BY
PData.entry
Antwoord 5, autoriteit 3%
Probeer dit
SELECT *
FROM people P
where P.IDNo in (SELECT DISTINCT IDNo
FROM people)
Antwoord 6, autoriteit 3%
Afhankelijk van de aard van de dubbele rijen, lijkt het erop dat u alleen hoofdlettergevoeligheid voor die kolommen wilt hebben. Het instellen van de sortering op deze kolommen zou moeten zijn wat u zoekt:
SELECT DISTINCT p.IDNO COLLATE SQL_Latin1_General_CP1_CI_AS, p.FirstName COLLATE SQL_Latin1_General_CP1_CI_AS, p.LastName COLLATE SQL_Latin1_General_CP1_CI_AS
FROM people P
http://msdn.microsoft.com/en-us/library/ ms184391.aspx
Antwoord 7
Gebruik Cross Apply of Outer Apply, op deze manier kunt u de hoeveelheid gegevens die moet worden samengevoegd uit de tabel met de duplicaten tot de eerste hit beperken.
Select
x.*,
c.*
from
x
Cross Apply
(
Select
Top (1)
IDNo,
FirstName,
LastName,
....,
from
people As p
where
p.idno = x.idno
Order By
p.idno //unnecessary if you don't need a specific match based on order
) As c
Cross Apply gedraagt zich als een inner join, Outer Apply als een left join