Eddig a saj\u00e1t aggreg\u00e1l\u00f3 f\u00fcggv\u00e9nyek csak 1 bemeneti v\u00e1ltoz\u00f3n dolgozhattak, most ezt a limitet feloldott\u00e1k. \u00cdgy meg\u00edrhatjuk magunk azokat a f\u00fcggv\u00e9nyeket, amelyek m\u00e1s term\u00e9kekben m\u00e1r benne vannak, \u00e9s amelyek nev\u00e9t nem mondjuk ki. :)<\/p>\n
P\u00e9ldak\u00e9nt mutatok egy Population Covariance sz\u00e1m\u00edt\u00f3 aggreg\u00e1l\u00f3 f\u00fcggv\u00e9nyt (a m\u00e1sik term\u00e9k doksij\u00e1b\u00f3l vettem a f\u00fcggv\u00e9ny sz\u00e1m\u00edt\u00e1si m\u00f3dj\u00e1t, de nem v\u00e1llalok r\u00e1 garanci\u00e1t, hogy j\u00f3l \u00edrtam \u00e1t CLR UDF-re):<\/p>\n
\r\nusing System;\r\nusing System.Data.SqlTypes;\r\nusing System.Runtime.InteropServices;\r\nusing Microsoft.SqlServer.Server;\r\n\r\n[Serializable]\r\n[SqlUserDefinedAggregate(\r\nFormat.Native, \/\/use clr serialization to serialize the intermediate result\r\nIsInvariantToNulls = true, \/\/the aggregate of S, {NULL} is the same as aggregate of S\r\nIsInvariantToDuplicates = false, \/\/optimizer property\r\nIsInvariantToOrder = true, \/\/optimizer property\r\nIsNullIfEmpty = true)\r\n]\r\n[StructLayout(LayoutKind.Sequential)]\r\npublic class PopulationCovariance\r\n{\r\n double sumOfA;\r\n double sumOfB;\r\n double sumOfAMultB;\r\n ulong count;\r\n\r\n public void Init()\r\n {\r\n sumOfA = sumOfB = sumOfAMultB = 0;\r\n }\r\n\r\n public void Accumulate(SqlDouble a, SqlDouble b)\r\n {\r\n if (a.IsNull || b.IsNull)\r\n {\r\n return;\r\n }\r\n\r\n sumOfA += a.Value;\r\n sumOfB += b.Value;\r\n sumOfAMultB += a.Value * b.Value;\r\n count++;\r\n }\r\n\r\n public void Merge(PopulationCovariance other)\r\n {\r\n sumOfA += other.sumOfA;\r\n sumOfB += other.sumOfB;\r\n sumOfAMultB += other.sumOfAMultB;\r\n count += other.count;\r\n }\r\n\r\n public SqlDouble Terminate()\r\n {\r\n if (count == 0)\r\n {\r\n return SqlDouble.Null;\r\n }\r\n\r\n return (sumOfAMultB - sumOfA * sumOfB \/ count) \/ count;\r\n }\r\n}\r\n<\/pre>\nMiel\u0151tt bemutatn\u00e1m a f\u00fcggv\u00e9ny haszn\u00e1lat\u00e1t el\u0151re bocs\u00e1jtom, hogy nem \u00e9rtek a statisztikai sz\u00e1m\u00edt\u00e1sokhoz, de felt\u00e9telezem a f\u00fcggv\u00e9ny kapcsolatot, \u00f6sszef\u00fcgg\u00e9st keres a k\u00e9t halmaz k\u00f6z\u00f6tt.<\/p>\n
\r\ncreate aggregate PopulationCovariance(@a float, @b float) \r\nreturns float\r\nexternal name SqlClrTest.PopulationCovariance\r\n\r\nif object_id('TestOrder') is not null drop table TestOrder\r\n\r\ncreate table TestOrder\r\n(\r\n Id int not null primary key identity,\r\n Discount float,\r\n OrderedAmount int\r\n)\r\n\r\ninsert TestOrder (Discount, OrderedAmount)\r\nvalues \r\n(0, 5),\r\n(10, 23),\r\n(5, 12),\r\n(2, 1),\r\n(14, 42)\r\n\r\nselect dbo.PopulationCovariance(Discount, OrderedAmount) from TestOrder\r\n\r\ntruncate table TestOrder\r\ninsert TestOrder (Discount, OrderedAmount)\r\nvalues \r\n(10, 5),\r\n(1, 23),\r\n(2, 12),\r\n(20, 1),\r\n(4, 42)\r\n\r\nselect dbo.PopulationCovariance(Discount, OrderedAmount) from TestOrder\r\n\r\ntruncate table TestOrder\r\ninsert TestOrder (Discount, OrderedAmount)\r\nvalues \r\n(10, 5),\r\n(0, 23),\r\n(2, 12),\r\n(5, 1),\r\n(14, 42)\r\n\r\nselect dbo.PopulationCovariance(Discount, OrderedAmount) from TestOrder\r\n<\/pre>\n\r\n73.08\r\n-65.84\r\n30.48\r\n<\/pre>\nAz els\u0151 adathalmaz alapj\u00e1n ha nagy a diszkont \u00e9rt\u00e9ke, t\u00f6bbet vesznek az emberek a term\u00e9kekb\u0151l. A m\u00e1sodikban pont ford\u00edtva hat a diszkont. A harmadikban pr\u00f3b\u00e1ltam \u00f6sszezagyv\u00e1lni az adatokat, ott kisebb, de pozit\u00edv a k\u00e9t halmaz f\u00fcgg\u00e9se. Valaki megszak\u00e9rthet\u00e9 nekem ezeket a sz\u00e1mokat, hogy j\u00f3l \u00e9rtelmezem-e? Gondolom valahogyan normaliz\u00e1lni kellene a kimenetet, de nem ismerem a szab\u00e1lyokat.<\/p>\n","protected":false},"excerpt":{"rendered":"
Eddig a saj\u00e1t aggreg\u00e1l\u00f3 f\u00fcggv\u00e9nyek csak 1 bemeneti v\u00e1ltoz\u00f3n dolgozhattak, most ezt a limitet feloldott\u00e1k. \u00cdgy meg\u00edrhatjuk magunk azokat a f\u00fcggv\u00e9nyeket, amelyek…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,58],"tags":[],"class_list":["post-458","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/458","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=458"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/458\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=458"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=458"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=458"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}