2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for users of TeX, LaTeX, ConTeXt, and related typesetting system

DECLARE @BinSize REAL;
DECLARE @NBins INTEGER;

-- After discarding users with < 150 reputation as outliers, calculate
-- an optimal number of histogram bins by applying Doane's Formula.
-- https://en.wikipedia.org/wiki/Histogram#Number_of_bins_and_width
WITH AvidUsers AS (
    SELECT Id
         , Reputation
         , LOG10(Reputation) AS Log10Reputation
        FROM Users
        WHERE Reputation >= 150
), Mean AS (
    SELECT AVG(Log10Reputation) AS μ
        FROM AvidUsers
), Moments AS (
    SELECT COUNT(Id) AS n
         , μ
         , STDEV(Log10Reputation) AS σ
         , AVG(POWER(CAST(Log10Reputation - μ AS FLOAT), 4)) AS μ4
        FROM AvidUsers, Mean
        GROUP BY μ
), Kurtosis AS (
    SELECT n
         , μ4 / POWER(σ, 4) - 3 AS ɣ2
        FROM Moments
), DoanesFormula AS (
    SELECT CEILING(1 + LOG(n, EXP(1)) + LOG(1 + ɣ2 * SQRT(n / 6), EXP(1))) AS k
        FROM Kurtosis
), BinSize AS (
    SELECT MAX(Log10Reputation) / k AS BinSize
         , k
        FROM AvidUsers, DoanesFormula
        GROUP BY k
) SELECT @BinSize = BinSize, @NBins = k FROM BinSize;


-- Be sure to plot the histogram bins with 0 members as well.
-- e3 is just some arbitrary table with 27 rows.
WITH e1(n) AS ( -- 3
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), e2(n) AS ( -- 9
   SELECT a.* FROM e1 AS a CROSS JOIN e1 AS b
), e3(n) AS ( -- 81
   SELECT a.* FROM e2 AS a CROSS JOIN e2 AS b
), e4(n) AS ( -- @NBins
SELECT ROW_NUMBER() OVER (ORDER BY n) FROM e3
), Bins AS (
   SELECT POWER(10.0, @BinSize * n) AS Lb
        , @BinSize * (n + 0.5) AS Bin
        , POWER(10.0, @BinSize * (n + 1)) AS Ub
       FROM e4
       WHERE
           n <= @NBins + 1
           AND POWER(10.0, @BinSize * (n + 1)) > 150
), AvidUsers AS (
    SELECT Id
         , Reputation
         , LOG10(Reputation) AS Log10Reputation
        FROM Users
)
SELECT Bin AS Log10Reputation
     , LOG10(COUNT(*)) AS Log10Count
    FROM Bins
        LEFT OUTER JOIN AvidUsers
            ON Lb <= Reputation AND Reputation < Ub
    GROUP BY Bin
    ORDER BY Bin;

fork query

edited sep 13 14

created jul 5 14 200_success