PostgreSQL Antipatterns: "Dole ne a sami ɗaya kawai!"
A cikin SQL, kuna bayyana "abin da" kuke son cimmawa, ba "yadda" yakamata a aiwatar dashi ba. Don haka, matsalar haɓaka tambayoyin SQL a cikin salon "kamar yadda ake ji shine yadda aka rubuta" ya ɗauki matsayinsa na girmamawa, tare da. fasali na lissafin yanayi a cikin SQL.
A yau, ta yin amfani da misalai masu sauƙi, bari mu ga abin da wannan zai iya haifar da shi a cikin mahallin amfani GROUP/DISTINCT и LIMIT tare da su.
Yanzu, idan kun rubuta a cikin buƙatar “Da farko ku haɗa waɗannan alamun, sannan ku jefar da duk kwafin, saura daya kawai kwafi ga kowane maɓalli" - wannan shine ainihin yadda zai yi aiki, koda kuwa ba a buƙatar haɗin kai kwata-kwata.
Kuma wani lokacin kuna da sa'a kuma yana "kawai yana aiki", wani lokacin yana da tasiri mara kyau akan aikin, kuma wani lokacin yana ba da tasirin da ba a tsammani gaba ɗaya daga ra'ayin mai haɓakawa.
To, watakila ba abin ban mamaki ba ne, amma ...
"Ma'aurata masu dadi": JOIN + DISTINCT
SELECT DISTINCT
X.*
FROM
X
JOIN
Y
ON Y.fk = X.pk
WHERE
Y.bool_condition;
Zai bayyana abin da suke so zaɓi rikodin X wanda akwai bayanai a cikin Y waɗanda ke da alaƙa da yanayin da aka cika. Rubuta bukata ta hanyar JOIN - sami wasu ƙimar pk sau da yawa (daidai nawa shigarwar da suka dace suka bayyana a cikin Y). Yadda za a cire? Tabbas DISTINCT!
Abin farin ciki ne musamman lokacin da kowane rikodin X akwai rikodin Y-rikodi masu alaƙa da yawa, sannan ana cire kwafin kwafin da jaruntaka ...
Yadda za a gyara? Da farko, gane cewa matsalar za a iya gyara zuwa "zaba records X wanda a cikin Y akwai A KALLA DAYA hade da cikar yanayin" - Bayan haka, ba ma buƙatar wani abu daga Y-rikodin kanta.
EXISTS
SELECT
*
FROM
X
WHERE
EXISTS(
SELECT
NULL
FROM
Y
WHERE
fk = X.pk AND
bool_condition
LIMIT 1
);
Wasu nau'ikan PostgreSQL sun fahimci cewa a cikin EXISTS ya isa nemo shigarwar farko da ta fito, tsofaffi ba sa. Don haka na fi son in nuna ko da yaushe LIMIT 1 a ciki EXISTS.
SHIGA LATERAL
SELECT
X.*
FROM
X
, LATERAL (
SELECT
Y.*
FROM
Y
WHERE
fk = X.pk AND
bool_condition
LIMIT 1
) Y
WHERE
Y IS DISTINCT FROM NULL;
Ƙarin fa'idar irin waɗannan sauye-sauyen tambayar ita ce ikon iya iyakance bincike cikin sauƙi idan ana buƙatar ɗaya ko kaɗan daga cikinsu, kamar a cikin yanayi mai zuwa:
SELECT DISTINCT ON(X.pk)
*
FROM
X
JOIN
Y
ON Y.fk = X.pk
LIMIT 1;
Yanzu mun karanta buƙatun kuma muna ƙoƙarin fahimtar abin da DBMS ke shirin yi:
haɗa alamun
na musamman ta X.pk
daga sauran abubuwan shigarwa, zaɓi ɗaya
To me kuka samu? "shiga daya kawai" daga na musamman - kuma idan muka ɗauki wannan ɗaya daga cikin waɗanda ba na musamman ba, shin sakamakon zai canza ko ta yaya?.. "Kuma idan babu bambanci, me yasa za a biya ƙarin?"
SELECT
*
FROM
(
SELECT
*
FROM
X
-- сюда можно подсунуть подходящих условий
LIMIT 1 -- +1 Limit
) X
JOIN
Y
ON Y.fk = X.pk
LIMIT 1;
Kuma daidai wannan batu tare da GROUP BY + LIMIT 1.
"Dole ne in yi tambaya": GROUP + LIMIT a fakaice
Abubuwa iri ɗaya suna faruwa a daban-daban duban rashin wofi Alamomi ko CTEs yayin da buƙatar ke ci gaba:
...
CASE
WHEN (
SELECT
count(*)
FROM
X
LIMIT 1
) = 0 THEN ...
Ayyukan tarawa (count/min/max/sum/...) an yi nasarar aiwatar da su a kan dukkan saitin, ko da ba tare da takamaiman umarni ba GROUP BY. Kawai tare da LIMIT ba su da abokantaka sosai.
Mai haɓakawa na iya tunani "Idan akwai bayanai a can, to, ba na buƙatar fiye da LIMIT". Amma kada ku yi haka! Domin ga tushe shine:
kirga abinda suke so bisa ga dukkan bayanan
ba da layukan da yawa kamar yadda suka tambaya
Ya danganta da yanayin da aka yi niyya, ya dace a yi ɗaya daga cikin abubuwan da ke biyowa:
(count + LIMIT 1) = 0a kanNOT EXISTS(LIMIT 1)
(count + LIMIT 1) > 0a kanEXISTS(LIMIT 1)
count >= Na kan(SELECT count(*) FROM (... LIMIT N))
"Nawa don rataya a cikin gram": DISTINCT + LIMIT
SELECT DISTINCT
pk
FROM
X
LIMIT $1
Mai haɓaka butulci yana iya yin imani da gaske cewa buƙatar za ta daina aiwatarwa. da zaran mun sami $1 na farko daban-daban dabi'u da suka zo a kan.
Wani lokaci a nan gaba wannan yana iya kuma zai yi aiki godiya ga sabon kumburi Fihirisar Tsallake Scan, wanda a halin yanzu ake aiwatar da aikin, amma ba tukuna ba.
A yanzu da farko za a dawo da duk bayanan, su ne na musamman, kuma daga gare su ne kawai za a mayar da adadin da aka nema. Yana da matukar bakin ciki idan muna son wani abu kamar $ 1 = 4, kuma akwai dubban daruruwan bayanai a cikin tebur ...