{"id":1903,"date":"2015-01-20T18:56:53","date_gmt":"2015-01-20T16:56:53","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1903"},"modified":"2015-01-20T18:56:53","modified_gmt":"2015-01-20T16:56:53","slug":"sql-fejtoro-5-megoldas","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2015\/01\/20\/sql-fejtoro-5-megoldas\/","title":{"rendered":"SQL fejt\u00f6r\u0151 5. megold\u00e1s"},"content":{"rendered":"<p>Egyszer\u0171 csoportos\u00edt\u00f3s <a href=\"http:\/\/soci.hu\/blog\/index.php\/2015\/01\/13\/sql-fejtoro-5\/\">p\u00e9lda<\/a>.<\/p>\n<p>A legk\u00e9zenfekv\u0151bb megold\u00e1s egy sima group by \u00e9s min:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nselect E.Nev, Telefon = min(E.Telefon) from Ember E group by E.Nev\r\n<\/pre>\n<p>Ezt egy covering nc indexszel sz\u00e9pen fel lehet gyors\u00edtani:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE NONCLUSTERED INDEX &#x5B;IX_1] ON &#x5B;dbo].&#x5B;Ember]\r\n(\r\n\t&#x5B;Nev] ASC\r\n)\r\nINCLUDE (&#x5B;Telefon]) \r\n<\/pre>\n<p>Egy m\u00e1sik megold\u00e1s m\u00e1r k\u00f6lts\u00e9gesebb, de helyes eredm\u00e9nyt ad (a komment szerz\u0151je szerintem ismerte az els\u0151t, csak valami tr\u00fckk\u00f6sebb megold\u00e1st akart adni):<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nselect *\r\nfrom Ember e1\r\nwhere not exists (\r\nselect *\r\nfrom Ember e2\r\nwhere e1.Nev = e2.Nev\r\nand e1.Telefon &gt; e2.Telefon\r\n)\r\n<\/pre>\n<p>Ennek logik\u00e1ja m\u00e1r k\u00f6r\u00fclm\u00e9nyesebb, k\u00e9rem azokat a sorokat, amelyekhez nem l\u00e9tezik olyan sor, aminek ugyanaz a neve, de kisebb a telefonsz\u00e1ma. Ezt csak joinnal tudja megoldani a szerver, jelent\u0151sen nagyobb k\u00f6lts\u00e9ggel.<\/p>\n<p>Egy jelent\u0151sen m\u00e1s logik\u00e1j\u00fa megold\u00e1s is \u00e9rkezett:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n;WITH Data AS\r\n(\r\n\tSELECT Nev, Telefon, ROW_NUMBER() OVER (PARTITION BY Nev ORDER BY Telefon) AS RowNumber\r\n\tFROM Ember\r\n)\r\nSELECT Nev, Telefon\r\nFROM Data\r\nWHERE RowNumber = 1\r\n<\/pre>\n<p>Felsorsz\u00e1mozzuk a sorokat a Telefon szerint rendezve, part\u00edcion\u00e1lva a Nev alapj\u00e1n, majd ebb\u0151l a halmazb\u00f3l kiszedj\u00fck azokat a sorokat, amelyek sorsz\u00e1ma 1. Minden Nev-hez 1 ilyen lesz, \u00e9rtelemszer\u0171en.<br \/>\nEz a megold\u00e1s nem szereti az el\u0151bbi index\u00fcnket. Ahhoz, hogy ez is gyors legyen, a Telefon oszlopot is bele kell rakni az index kulcsai k\u00f6z\u00e9:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE NONCLUSTERED INDEX &#x5B;IX_1] ON &#x5B;dbo].&#x5B;Ember]\r\n(\r\n\t&#x5B;Nev] ASC,\r\n\t&#x5B;Telefon] ASC\r\n)\r\n<\/pre>\n<p>\u00cdgy a group by-os \u00e9s ezen megold\u00e1s k\u00f6lts\u00e9ge m\u00e1r azonos lesz, b\u00e1r a terv\u00fck nem. Ez esetben sok sor eset\u00e9n lehetne eld\u00f6nteni, van-e perf k\u00fcl\u00f6nbs\u00e9g a k\u00e9t megold\u00e1s k\u00f6z\u00f6tt, akinek van erre ideje, \u00e9rdekelne az eredm\u00e9nye. Mondjuk 10 milli\u00f3 sorra. \u00c9rz\u00e9sre nem lesz, de c\u00e1foljatok meg, konkr\u00e9t terveket mutatva.<\/p>\n<p>K\u00f6sz\u00f6n\u00f6m mindenkinek a megold\u00e1sokat, \u00e9s k\u00fcl\u00f6n k\u00f6sz\u00f6n\u00f6m Moln\u00e1r Csab\u00e1nak, hogy a nem nyilv\u00e1nval\u00f3 megold\u00e1st is megmutatta. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Egyszer\u0171 csoportos\u00edt\u00f3s p\u00e9lda. A legk\u00e9zenfekv\u0151bb megold\u00e1s egy sima group by \u00e9s min: select E.Nev, Telefon = min(E.Telefon) from Ember E group by&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30],"tags":[],"class_list":["post-1903","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1903","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1903"}],"version-history":[{"count":1,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1903\/revisions"}],"predecessor-version":[{"id":1904,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1903\/revisions\/1904"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1903"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1903"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1903"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}