Click or drag to resize
sqx

FIVESUM Structure

Aggregate [Vector] FIVESUM([float] Value, [smallint] Method)

Namespace:  sqx
Assembly:  SQX (in SQX.dll) Version: 1.0.2.6 (1.0.2.6)
Syntax
C#
Copy
[SerializableAttribute]
public struct FIVESUM : IBinarySerialize
Remarks
Five numbers summary aggregate function.
Quartile Method:
  • 1 - This rule is also applied by QUARTIL.EXC function in Excel after Office 2010.
  • 2 - (Tukey's hinges). This rule is applied by Excel before Office 2010 and by the QUARTIL.INC function after Office 2010.
  • 3 - For odd N arithmetic mean of Methods 1 and 2, else Method 2.

NOTE: All parameters except Value must remain constant within the GROUP BY.

Attributes

Format.UserDefined, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true, MaxByteSize = -1
Examples
SQL
Copy
WITH sample_data AS (
SELECT g.Value AS MyGroup, v.ID, sqx.StdLogistic_PPF(v.Value) AS X
FROM sqx.NTable(0,5) g
CROSS APPLY sqx.UTable(1000, g.Value) v
)
SELECT MyGroup, N, summary.*, fences.*
FROM (
    SELECT MyGroup, COUNT(X) N, sqx.FIVESUM(X,2) FS
    FROM sample_data
    GROUP BY MyGroup
) a
CROSS APPLY sqx.FiveNumbersSummaryTable(a.FS) summary
CROSS APPLY sqx.FiveNumbersSummaryFencesTable(a.FS, 1.5, 3) fences
ORDER BY MyGroup
/*
MyGroup     N           SampleMin              SampleQ1               SampleQ2               SampleQ3               SampleMax              OuterLowerFence        InnerLowerFence        InnerUpperFence        OuterUpperFence        InnerScale             OuterScale             Iqr                    Qcd                    Trimean
----------- ----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
0           1000        -8.09963861717956      -1.17416129057231      -0.0138290596074119    1.02302380924215       10.5085164484053       -7.76571659001571      -4.46993894029401      4.31880145896385       7.61457910868555       1.5                    3                      2.19718509981447       14.5376585640902       -0.0446989001362467
1           1000        -8.11673060413699      -1.10276815775652      0.042487900291306      1.09793728225056       10.1005221752533       -7.70488447777776      -4.40382631776714      4.39899544226118       7.7000536022718        1.5                    3                      2.20070544000708       455.5500213771         0.0200362312691647
2           1000        -8.2322993405841       -1.02542619431166      0.104146982060515      1.24745497607396       7.35260128999571       -7.84406970546852      -4.43474794989009      4.65677673165239       8.06609848723082       1.5                    3                      2.27288117038562       10.2368762839897       0.107580686470833
3           1000        -6.55727657880066      -1.09530827128168      -0.0344550953124547    1.03269451194606       8.16922260121061       -7.47931662096491      -4.28731244612329      4.22469868678767       7.41670286162929       1.5                    3                      2.12800278322774       33.9861845991601       -0.0328809874901322
4           1000        -7.58086257335985      -1.10523630931603      -0.0208404525757162    0.998259920387946      7.05068761165108       -7.41572499842796      -4.260480653872        4.15350426494391       7.30874860949988       1.5                    3                      2.10349622970398       19.6631822290999       -0.0371643235198791
5           1000        -6.68444305127997      -1.10811262019772      -0.0600091114296069    1.12112864354874       8.23047538162706       -7.79583641143711      -4.45197451581742      4.46499053916844       7.80885243478814       1.5                    3                      2.22924126374646       171.268996960659       -0.0267505498770473*/
See Also