{"id":2245,"date":"2012-07-11T13:03:59","date_gmt":"2012-07-11T13:03:59","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2245"},"modified":"2012-07-11T13:03:59","modified_gmt":"2012-07-11T13:03:59","slug":"%e4%bd%bf%e7%94%a8sql%e7%94%9f%e6%88%90%e9%9d%9e%e5%9d%87%e5%8c%80%e9%9a%8f%e6%9c%ba%e6%95%b0","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/07\/11\/%e4%bd%bf%e7%94%a8sql%e7%94%9f%e6%88%90%e9%9d%9e%e5%9d%87%e5%8c%80%e9%9a%8f%e6%9c%ba%e6%95%b0\/","title":{"rendered":"\u4f7f\u7528SQL\u751f\u6210\u975e\u5747\u5300\u968f\u673a\u6570"},"content":{"rendered":"

\u6b63\u5982”\u968f\u673a\u6570\u7684\u751f\u6210\u8fc7\u7a0b\u975e\u5e38\u91cd\u8981\u56e0\u6b64\u6211\u4eec\u4e0d\u80fd\u5bf9\u5176\u89c6\u800c\u4e0d\u89c1”(\u5f15\u81eaRobert R -\u6a61\u6811\u5cad\u56fd\u5bb6\u5b9e\u9a8c\u5ba4),\u6211\u4eec\u5e0c\u671b\u5360\u7528\u60a8\u4e00\u70b9\u70b9\u5b9d\u8d35\u7684\u65f6\u95f4\u5728\u57fa\u4e8eSQL Server MVP Jeff Moden<\/a>\u7684 \u6210\u679c\u7684\u57fa\u7840\u4e0a\u5b8c\u6210\u8fd9\u9879\u5de5\u4f5c\u3002\u5bf9\u4e8e\u4f7f\u7528SQL\u6765\u4ea7\u751f\u968f\u673a\u6570\u6765\u8bf4\uff0c\u6211\u4eec\u4f1a\u91cd\u70b9\u8bb2\u89e3\u4ece\u5747\u5300\u5206\u5e03\u968f\u673a\u6570(non-uniformly distributed random numbers)\u7684\u57fa\u7840\u4e0a\u751f\u6210\u975e\u5747\u5300\u5206\u5e03\u968f\u673a\u6570(uniformly distributed random numbers);\u5305\u62ec\u4e00\u4e9b\u7edf\u8ba1\u5206\u5e03\u7684\u57fa\u7840\u6765\u5e2e\u4f60\u8d77\u6b65\u3002<\/p>\n

\n\t    \u6b63\u5982\u6211\u4eec\u6240\u77e5\uff0c\u968f\u673a\u6570\u5728\u4eff\u771f\u4e2d\u975e\u5e38\u91cd\u8981(\u5c24\u5176\u662f\u8499\u7279\u5361\u6d1b\u4eff\u771f\u6cd5<\/a>\uff09,\u8fd8\u6709\u968f\u673a\u6570\u5728\u5bc6\u7801\u5b66<\/a>\u4ee5\u53ca\u5176\u5b83\u9ad8\u79d1\u6280\u9886\u57df\u4e2d\u4e5f\u626e\u6f14\u4e86\u540c\u6837\u91cd\u8981\u7684\u89d2\u8272\u3002\u9664\u6b64\u4e4b\u5916\u5728\u6211\u4eec\u7684SQL Server\u4e2d\u6709\u65f6\u4e5f\u9700\u8981\u4ea7\u751f\u5927\u91cf\u7684\u968f\u673a\u6570\u636e\u6765\u6d4b\u8bd5SQL\u7684\u6027\u80fd\u3002<\/p>\n

\n\t    \u56e0\u4e3a\u6211\u5e76\u4e0d\u662f\u7edf\u8ba1\u5b66\u5bb6\uff0c\u56e0\u6b64\u6211\u4eec\u8fd9\u91cc\u4ec5\u4ec5\u6765\u770b\u7528SQL\u751f\u6210\u5e76\u4e14\u80fd\u663e\u800c\u6613\u89c1\u7684\u770b\u51fa\u5176\u968f\u673a\u6027\u7684\u7684\u968f\u673a\u6570\u5b57,\u800c\u5e76\u4e0d\u4f1a\u6df1\u5165\u5230\u6570\u5b66\u539f\u7406\u6765\u770b\u8fd9\u4e2a\u968f\u673a\u6027\u662f\u771f\u6b63\u7684“\u968f\u673a”\u8fd8\u662f“\u8c8c\u4f3c\u968f\u673a”\u6211\u4eec\u7684\u610f\u56fe\u662f\u6587\u7ae0\u4e2d\u7b97\u6cd5\u7684\u6b63\u786e\u6027\u4ee5\u53ca\u8fd9\u4e2a\u7b97\u6cd5\u5728\u975e\u5173\u952e\u9886\u57df\u662f\u5426\u8db3\u591f\u4f7f\u7528\u3002<\/p>\n

\n\t   \u901a\u5e38\u6765\u8bf4\uff0c\u7531\u5747\u5300\u968f\u673a\u6570\u8f6c\u6362\u6210\u975e\u5747\u5300\u968f\u673a\u6570<\/a>\u7684\u6280\u672f\u662f\u5c06\u5747\u5300\u968f\u673a\u6570\u4e58\u4ee5\u7d2f\u8ba1\u5206\u5e03\u51fd\u6570(CDF)<\/a>\u5bf9\u4e8e\u76ee\u6807\u6570\u636e\u7684\u53cd\u8f6c\u3002\u4f46\u5728\u5b9e\u8df5\u4e2d\uff0c\u7d2f\u8ba1\u5206\u5e03\u51fd\u6570\u662f\u5426\u9488\u5bf9\u7279\u5b9a\u5206\u5e03\u5b58\u5728\u6709\u6548\u54ea\u6015\u662f\u63a5\u8fd1\u7684\u51fd\u6570\u5e76\u4e0d\u597d\u4f30\u8ba1\u3002\u4f46\u5e78\u8fd0\u7684\u662f\uff0c\u6bd4\u6211\u4eec\u806a\u660e\u8bb8\u591a\u7684\u90a3\u5e2e\u5bb6\u4f19\u5df2\u7ecf\u5206\u6790\u8fc7\u4e86\u591a\u4e2a\u9886\u57df\u7684\u591a\u79cd\u5206\u5e03\u51fd\u6570\u6211\u4eec\u53ef\u4ee5\u76f4\u63a5\u62ff\u6765\u4f7f\u7528,\u8fd9\u4e9b\u51fd\u6570\u53ef\u4ee5\u6ee1\u8db3\u6211\u4eec\u7684\u5927\u591a\u6570\u9700\u6c42\u3002<\/p>\n

\n\t <\/p>\n

\n\t\u6d4b\u8bd5\u5de5\u5177<\/font><\/h3>\n

\n\t    \u5728\u6211\u4eec\u7684\u6d4b\u8bd5\u4e2d\uff0c\u6211\u4eec\u91c7\u7528\u6807\u51c6\u7684SQL\u6280\u672f\u6765\u4f7f\u7528\u4f2aURN(\u5747\u5300\u5206\u5e03\u968f\u673a\u6570)\u51fd\u6570\u751f\u6210Float\u7c7b\u578b\u7684\u53c2\u6570\u4f20\u7ed9\u8f6c\u6362\u51fd\u6570.,\u6211\u4eec\u5c06\u4f7f\u7528\u6807\u91cf(Scalar)\u51fd\u6570<\/a>\u5305\u62ecSCHEMABINDING\u5173\u952e\u5b57\u89e3\u51b3\u6027\u80fd\u95ee\u9898<\/a>\u7136\u800c\uff0c\u6216\u8bb8\u4f60\u8fd8\u60f3\u4f7f\u7528\u540c\u7b49\u7684\u8868\u503c\u51fd\u6570<\/a>\u6765\u6d4b\u8bd5\u6027\u80fd\u662f\u5426\u8fd8\u53ef\u4ee5\u8fdb\u4e00\u6b65\u63d0\u5347\u3002\u9996\u5148\uff0c\u6765\u751f\u6210\u6d4b\u8bd5\u6570\u636e\u3002<\/p>\n

\n\t   <\/p>\n

\n
\r\n-- Data definition and setup<\/span>\r\nDECLARE<\/a> @NumberOfRNs   INT<\/a>\r\n       ,@Lambda        FLOAT<\/a>     -- For the Poisson NURNs<\/span>\r\n       ,@GaussianMean  FLOAT<\/a>     -- For the Normal NURNs<\/span>\r\n       ,@GaussianSTDEV FLOAT<\/a>\r\n       ,@LambdaEXP     FLOAT<\/a>     -- For the Exponential NURNs<\/span>\r\n       ,@WeibullAlpha  FLOAT<\/a>     -- For the Weibull NURNs<\/span>\r\n       ,@WeibullBeta   FLOAT<\/a>\r\n       ,@Laplaceu      FLOAT<\/a>     -- For the Laplace NURNs<\/span>\r\n       ,@Laplaceb      FLOAT<\/a>\r\n\r\nSELECT<\/a> @NumberOfRNs    = 10000\r\n      ,@Lambda         = 4.0     -- Lambda for the Poisson Distribution<\/span>\r\n      ,@GaussianMean   = 5       -- Mean for the Normal Distribution<\/span>\r\n      ,@GaussianSTDEV  = 1.5     -- Standard Deviation for the Normal Distribution<\/span>\r\n      ,@LambdaEXP      = 1.5     -- Lambda for the Exponential Distribution<\/span>\r\n      ,@WeibullAlpha   = 1.0     -- Alpha (scale) for the Weibull Distribution<\/span>\r\n      ,@WeibullBeta    = 1.5     -- Beta (shape) for the Weibull Distribution<\/span>\r\n      ,@Laplaceu       = 4.0     -- Mu (location) for the Laplace Distribution<\/span>\r\n      ,@Laplaceb       = 1.0     -- Beta (scale) for the Laplace Distribution<\/span>\r\n\r\n--CREATE TYPE Distribution AS TABLE (EventID INT, EventProb FLOAT, CumProb FLOAT)<\/span>\r\n\r\nDECLARE<\/a> @Binomial      AS<\/a> Distribution\r\n    ,@DUniform         AS<\/a> Distribution\r\n    ,@Multinomial      AS<\/a> Distribution\r\n\r\n-- Simulate a coin toss with a Binomial Distribution<\/span>\r\nINSERT<\/a> INTO<\/a> @Binomial\r\nSELECT<\/a> 0, 0.5, 0.5 UNION<\/a> ALL<\/a> SELECT<\/a> 1, 0.5, 1.0\r\n\r\n-- Events returned by this Discrete Uniform distribution are the 6<\/span>\r\n-- Fibonacci numbers starting with the second occurrence of 1<\/span>\r\nINSERT<\/a> INTO<\/a> @DUniform\r\nSELECT<\/a> 1, 1.\/6., 1.\/6. UNION<\/a> ALL<\/a> SELECT<\/a> 2, 1.\/6., 2.\/6.\r\nUNION<\/a> ALL<\/a> SELECT<\/a> 3, 1.\/6., 3.\/6. UNION<\/a> ALL<\/a> SELECT<\/a> 5, 1.\/6., 4.\/6.\r\nUNION<\/a> ALL<\/a> SELECT<\/a> 8, 1.\/6., 5.\/6. UNION<\/a> ALL<\/a> SELECT<\/a> 13, 1.\/6., 1.\r\n\r\n-- Events returned by this Multinomial distribution are the 5<\/span>\r\n-- Mersenne primes discovered in 1952 by Raphael M. Robinson<\/span>\r\nINSERT<\/a> INTO<\/a> @Multinomial\r\nSELECT<\/a> 521, .10, .10 UNION<\/a> ALL<\/a> SELECT<\/a> 607, .25, .35 UNION<\/a> ALL<\/a> SELECT<\/a> 1279, .30, .65\r\nUNION<\/a> ALL<\/a> SELECT<\/a> 2203, .15, .80 UNION<\/a> ALL<\/a> SELECT<\/a> 2281, .2, 1.<\/pre>\n<\/div>\n
            \uff08\u8bd1\u8005\u6ce8\uff1a\u6211\u5c06@NumberOfRNs \u53c2\u6570\u7531100W\u6539\u62101W\uff0c\u56e0\u4e3a100W\u5728\u6d4b\u8bd5\u4e2d\u5360\u7528\u7684\u65f6\u95f4\u8fc7\u957f\uff0c\u5982\u679c\u4f60\u559c\u6b22\uff0c\u53ef\u4ee5\u968f\u610f\u6539\u52a8\u3002\uff09<\/p>\n
\n\t   \u4e0b\u9762\u662f\u6d4b\u8bd5\u5de5\u5177\uff0c\u4e3a\u4e86\u6211\u4eec\u671f\u671b\u7684\u76ee\u6807\u5206\u5e03\u751f\u6210NURNs(\u975e\u5747\u5300\u5206\u5e03\u968f\u673a\u6570):<\/p>\n
\n
\r\n-- Create random numbers for the selected distributions<\/span>\r\nSELECT<\/a> TOP<\/a> (@NumberOfRNs)\r\n     RandomUniform     = URN\r\n    --,RandomPoisson     = dbo.RN_POISSON(@Lambda, URN)<\/span>\r\n    ,RandomBinomial    = dbo.RN_MULTINOMIAL(@Binomial, URN)\r\n    ,RandomDUniform    = dbo.RN_MULTINOMIAL(@DUniform, URN)\r\n    ,RandomMultinomial = dbo.RN_MULTINOMIAL(@Multinomial, URN)\r\n    ,RandomNormal      = dbo.RN_NORMAL(@GaussianMean, @GaussianSTDEV, URN, RAND(CHECKSUM(NEWID())))\r\n    ,RandomExponential = dbo.RN_EXPONENTIAL(@LambdaEXP, URN)\r\n    ,RandomWeibull     = dbo.RN_WEIBULL(@WeibullAlpha, @WeibullBeta, URN)\r\n    ,RandomLaplace     = dbo.RN_LAPLACE(@Laplaceu, @Laplaceb, URN)\r\n INTO<\/a> #MyRandomNumbers\r\n FROM<\/a> sys.all_columns a1 CROSS<\/a> APPLY sys.all_columns a2\r\n CROSS<\/a> APPLY (SELECT<\/a> RAND(CHECKSUM(NEWID()))) URN(URN)<\/pre>\n<\/div>\n
<\/p>\n
\n\t <\/p>\n
\n\t       \u63a5\u4e0b\u6765\uff0c\u6211\u4eec\u5c06\u4f1a\u9010\u4e2a\u4ecb\u7ecd\u6bcf\u79cd\u5206\u5e03\u7c7b\u578b\uff0c\u4f46\u662f\u5728\u6b64\u4e4b\u524d\uff0c\u9996\u5148\u9610\u8ff0\u4e00\u4e0b\u5173\u4e8e\u6211\u4eec\u7684\u6d4b\u8bd5\u5de5\u5177\u4f60\u53ef\u80fd\u4f1a\u95ee\u5230\u7684\u95ee\u9898:<\/p>\n