Average a column value based on depth interval

  • I have a drill hole table that looks like the table below -

    DataSetHole_IDDepth_FromDepth_ToAu_ppm
    TestDrill1011.23
    TestDrill1120.3
    TestDrill1230.02
    TestDrill1342.65
    TestDrill1450.6
    TestDrill1560.4
    TestDrill1670.98
    TestDrill1785.36
    TestDrill1890.48
    TestDrill19101.56

    I want to group intervals into 5m intervals and average the Au_ppm column. ie I want the end result to look like this -

    DataSetHole_IDDepth_FromDepth_ToAvg_Au_ppm
    TestDrill1050.96
    TestDrill15101.756

    Thanks for any help.

  • Thanks for the data, and expected results - it helped a lot.  I fixed it up and created the scripts to populate your table - (since you're new, you get a free pass, but read Jeff Moden's article on how to post a good question ).
    -- create table...
    CREATE TABLE #SomeData (
        Dataset CHAR(5),
        HoleID CHAR(7),
        Depth_From INT,
        Depth_To INT,
        AU_ppm DECIMAL(3,2)
    );
    GO
    -- then populate the table
    INSERT INTO #SomeData (DataSet, HoleID, Depth_From, Depth_To, AU_ppm)
    VALUES ('Test',    'Drill1',    0,    1,    1.23),
    ('Test', 'Drill1',    1,    2,    0.3),
    ('Test', 'Drill1',    2,    3,    0.02),
    ('Test',    'Drill1',    3,    4,    2.65),
    ('Test',    'Drill1',    4,    5,    0.6),
    ('Test',    'Drill1',    5,    6,    0.4),
    ('Test',    'Drill1',    6,    7,    0.98),
    ('Test',    'Drill1',    7,    8,    5.36),
    ('Test',    'Drill1',    8,    9,    0.48),
    ('Test',    'Drill1',    9,    10,    1.56);

    Now we have something to query... (I'm sure someone smarter than I will post a better solution, but this worked...)
    -- 5m intervals
    SELECT e.Dataset
        , e.HoleID
        , e.Grp
        , MIN(Depth_From) AS MinDepth
        , MAX(Depth_To) AS MaxDepth
        , AVG(e.AU_PPM) AS AvgPPM
    FROM
    (
    SELECT d.Dataset
        , d.HoleID
        , d.Depth_From
        , d.Depth_To
        , (d.Depth_To-1) / 5 As Grp
        , d.AU_ppm
    FROM
        (SELECT DataSet
            , HoleID
            , Depth_From
            , Depth_To
            , Depth_To - Depth_From AS DepthDelta
            , AU_ppm
        FROM #SomeData) d
    ) e
    GROUP BY e.Dataset
        , e.HoleID
        , e.Grp
    ORDER BY e.Grp;

  • Thanks so much for that. I will read the article as suggested.

  • We can simplify the query into a single select statement
    😎


    SELECT
      SD.Dataset      AS Dataset
     ,SD.HoleID      AS HoleID
     ,CEILING(SD.Depth_To * 0.2) AS GRP
     ,MIN(SD.Depth_From)   AS Depth_From
     ,MAX(SD.Depth_To)    AS Depth_To
     ,AVG(SD.AU_ppm)     AS AVG_AU_ppm
    FROM  #SomeData SD
    GROUP BY SD.Dataset
       ,SD.HoleID
       ,CEILING(SD.Depth_To * 0.2)
    ORDER BY CEILING(SD.Depth_To * 0.2);

  • For completeness, here is an alternative way of doing this, using a calculated column and an index to eliminate the sort operation. On a large data set, this is much more efficient.
    😎


    -- create table...
    CREATE TABLE #SomeData (
      Dataset CHAR(5),
      HoleID CHAR(7),
      Depth_From INT,
      Depth_To INT,
      AU_ppm DECIMAL(3,2),
      GRP_ID AS (CEILING(Depth_To * 0.2)) PERSISTED
    );
    GO
    -- then populate the table
    INSERT INTO #SomeData (DataSet, HoleID, Depth_From, Depth_To, AU_ppm)
    VALUES ('Test',  'Drill1',  0,  1,  1.23),
    ('Test', 'Drill1',  1,  2,  0.3),
    ('Test', 'Drill1',  2,  3,  0.02),
    ('Test',  'Drill1',  3,  4,  2.65),
    ('Test',  'Drill1',  4,  5,  0.6),
    ('Test',  'Drill1',  5,  6,  0.4),
    ('Test',  'Drill1',  6,  7,  0.98),
    ('Test',  'Drill1',  7,  8,  5.36),
    ('Test',  'Drill1',  8,  9,  0.48),
    ('Test',  'Drill1',  9,  10,  1.56);

    CREATE NONCLUSTERED INDEX NCLIDX_SOMEDATA_GRP_ID_DATASET_HOLEID_INCL_DFROM_DTO_AUPPM ON #SomeData (GRP_ID,Dataset,HoleID) INCLUDE (Depth_From,Depth_To,AU_ppm);

    SELECT
      SD.Dataset      AS Dataset
     ,SD.HoleID      AS HoleID
     ,GRP_ID AS GRP
     ,MIN(SD.Depth_From)   AS Depth_From
     ,MAX(SD.Depth_To)    AS Depth_To
     ,AVG(SD.AU_ppm)     AS AVG_AU_ppm
    FROM  #SomeData SD
    GROUP BY SD.Dataset
       ,SD.HoleID
       ,SD.GRP_ID
    ORDER BY SD.GRP_ID;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply