February 16, 2018 at 11:51 pm
I have a drill hole table that looks like the table below -
DataSet | Hole_ID | Depth_From | Depth_To | Au_ppm |
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 |
I want to group intervals into 5m intervals and average the Au_ppm column. ie I want the end result to look like this -
DataSet | Hole_ID | Depth_From | Depth_To | Avg_Au_ppm |
Test | Drill1 | 0 | 5 | 0.96 |
Test | Drill1 | 5 | 10 | 1.756 |
Thanks for any help.
February 17, 2018 at 12:50 am
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;
February 17, 2018 at 1:05 am
Thanks so much for that. I will read the article as suggested.
February 17, 2018 at 1:25 am
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);
February 17, 2018 at 2:40 am
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