 | EMPIRICAL Structure |
Aggregate [EmpiricalData] EMPIRICAL([float] Value, [float] Target)
Namespace: sqx
Assembly: SQX (in SQX.dll) Version: 1.0.2.6 (1.0.2.6)
SyntaxC#
[SerializableAttribute] public struct EMPIRICAL
RemarksEmpirical aggregate function.
NOTE: All parameters except Value must remain constant within the GROUP BY.
NOTE: All parameters except Value must remain constant within the GROUP BY.
Attributes
Format.Native, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = trueExamplesSQL
/* Example Histogram with int */ ;WITH sample_data AS ( SELECT sqx.Poisson_PPF(Value, 250.25) X FROM sqx.UTable(1000,1) ) SELECT a.[Value], et.Frequency, et.RelativeFrequency , et.EmpiricalCDF, et.EmpiricalQDF FROM ( SELECT b.[Value], sqx.EMPIRICAL(s.X, b.[Value]) EMP FROM (SELECT MAX(X) MaxValue FROM sample_data) p CROSS APPLY sqx.NTable(0, p.MaxValue) b, sample_data s GROUP BY b.[Value] --HAVING sqx.FREQUENCY(s.X, b.[Value]) > 0 ) a CROSS APPLY sqx.EmpiricalTable(a.EMP) et ORDER BY a.[Value]
SQL
/* Example Histogram with float [0,1]*/ DECLARE @Round int = 2 ;WITH sample_data AS (SELECT ID, Value X FROM sqx.UTable(10000, 0)) SELECT a.[Value], et.* FROM ( SELECT b.[Value], sqx.EMPIRICAL(ROUND(c.X, @Round), b.[Value]) EMP FROM sqx.XTable(0, 1, sqx.MakeStep(1, @Round)) b, sample_data c GROUP BY b.[Value] ) a CROSS APPLY sqx.EmpiricalTable(a.EMP) et ORDER BY a.[Value] /* Value Frequency RelativeFrequency EmpiricalCDF EmpiricalQDF ------------ ---------- ---------------------- ----------------- ----------------- 0 54 0.0054 0.0054 1 0.01 102 0.0102 0.0156 0.9946 0.02 111 0.0111 0.0267 0.9844 0.03 116 0.0116 0.0383 0.9733 0.04 80 0.008 0.0463 0.9617 0.05 107 0.0107 0.057 0.9537 0.06 100 0.01 0.067 0.943 0.07 117 0.0117 0.0787 0.933 0.08 94 0.0094 0.0881 0.9213 0.09 88 0.0088 0.0969 0.9119 0.1 91 0.0091 0.106 0.9031 0.11 109 0.0109 0.1169 0.894 0.12 112 0.0112 0.1281 0.8831 0.13 86 0.0086 0.1367 0.8719 0.14 86 0.0086 0.1453 0.8633 0.15 83 0.0083 0.1536 0.8547 0.16 108 0.0108 0.1644 0.8464 0.17 113 0.0113 0.1757 0.8356 0.18 110 0.011 0.1867 0.8243 0.19 106 0.0106 0.1973 0.8133 0.2 101 0.0101 0.2074 0.8027 0.21 93 0.0093 0.2167 0.7926 0.22 103 0.0103 0.227 0.7833 0.23 108 0.0108 0.2378 0.773 0.24 103 0.0103 0.2481 0.7622 0.25 90 0.009 0.2571 0.7519 0.26 97 0.0097 0.2668 0.7429 0.27 89 0.0089 0.2757 0.7332 0.28 108 0.0108 0.2865 0.7243 0.29 100 0.01 0.2965 0.7135 0.3 114 0.0114 0.3079 0.7035 0.31 83 0.0083 0.3162 0.6921 0.32 103 0.0103 0.3265 0.6838 0.33 102 0.0102 0.3367 0.6735 0.34 96 0.0096 0.3463 0.6633 0.35 104 0.0104 0.3567 0.6537 0.36 114 0.0114 0.3681 0.6433 0.37 99 0.0099 0.378 0.6319 0.38 96 0.0096 0.3876 0.622 0.39 95 0.0095 0.3971 0.6124 0.4 106 0.0106 0.4077 0.6029 0.41 85 0.0085 0.4162 0.5923 0.42 105 0.0105 0.4267 0.5838 0.43 99 0.0099 0.4366 0.5733 0.44 93 0.0093 0.4459 0.5634 0.45 115 0.0115 0.4574 0.5541 0.46 112 0.0112 0.4686 0.5426 0.47 92 0.0092 0.4778 0.5314 0.48 89 0.0089 0.4867 0.5222 0.49 85 0.0085 0.4952 0.5133 0.5 100 0.01 0.5052 0.5048 0.51 106 0.0106 0.5158 0.4948 0.52 117 0.0117 0.5275 0.4842 0.53 112 0.0112 0.5387 0.4725 0.54 86 0.0086 0.5473 0.4613 0.55 93 0.0093 0.5566 0.4527 0.56 105 0.0105 0.5671 0.4434 0.57 110 0.011 0.5781 0.4329 0.58 95 0.0095 0.5876 0.4219 0.59 93 0.0093 0.5969 0.4124 0.6 91 0.0091 0.606 0.4031 0.61 105 0.0105 0.6165 0.394 0.62 101 0.0101 0.6266 0.3835 0.63 92 0.0092 0.6358 0.3734 0.64 93 0.0093 0.6451 0.3642 0.65 104 0.0104 0.6555 0.3549 0.66 105 0.0105 0.666 0.3445 0.67 108 0.0108 0.6768 0.334 0.68 90 0.009 0.6858 0.3232 0.69 114 0.0114 0.6972 0.3142 0.7 104 0.0104 0.7076 0.3028 0.71 88 0.0088 0.7164 0.2924 0.72 122 0.0122 0.7286 0.2836 0.73 101 0.0101 0.7387 0.2714 0.74 96 0.0096 0.7483 0.2613 0.75 101 0.0101 0.7584 0.2517 0.76 99 0.0099 0.7683 0.2416 0.77 95 0.0095 0.7778 0.2317 0.78 82 0.0082 0.786 0.2222 0.79 82 0.0082 0.7942 0.214 0.8 105 0.0105 0.8047 0.2058 0.81 113 0.0113 0.816 0.1953 0.82 98 0.0098 0.8258 0.184 0.83 110 0.011 0.8368 0.1742 0.84 109 0.0109 0.8477 0.1632 0.85 104 0.0104 0.8581 0.1523 0.86 86 0.0086 0.8667 0.1419 0.87 93 0.0093 0.876 0.1333 0.88 111 0.0111 0.8871 0.124 0.89 94 0.0094 0.8965 0.1129 0.9 101 0.0101 0.9066 0.1035 0.91 103 0.0103 0.9169 0.0934 0.92 101 0.0101 0.927 0.0831 0.93 100 0.01 0.937 0.0729999999999999 0.94 104 0.0104 0.9474 0.063 0.95 96 0.0096 0.957 0.0526 0.96 79 0.0079 0.9649 0.043 0.97 95 0.0095 0.9744 0.0351 0.98 98 0.0098 0.9842 0.0256 0.99 107 0.0107 0.9949 0.0158 1 51 0.0051 1 0.0051*/
See Also