Hello, theres
I'm new in Elastic. Is it possible to get a drill down result in one query like SQL.
Suppose, I have some rainfall data in SQL statement follows. ( same mapping in Elastic)
USE tempdb
GO
DROP TABLE rainfall
GO
CREATE TABLE rainfall
( raindate SMALLDATETIME,
country VARCHAR(10),
area VARCHAR(10),
city VARCHAR(20),
fall DECIMAL(7,2),
CONSTRAINT pk_rainfall PRIMARY KEY ( raindate, country, city )
)
GO
INSERT INTO rainfall SELECT '2016-01-01', 'country1','area1', 'city1', 10
INSERT INTO rainfall SELECT '2016-01-02', 'country1','area1', 'city1', 20
INSERT INTO rainfall SELECT '2016-01-03', 'country1','area1', 'city1', 12
INSERT INTO rainfall SELECT '2016-01-04', 'country1','area1', 'city1', 15
INSERT INTO rainfall SELECT '2016-01-05', 'country1','area1', 'city1', 16
INSERT INTO rainfall SELECT '2016-01-01', 'country1','area1', 'city2', 14
INSERT INTO rainfall SELECT '2016-01-02', 'country1','area1', 'city2', 15
INSERT INTO rainfall SELECT '2016-01-03', 'country1','area1', 'city2', 16
INSERT INTO rainfall SELECT '2016-01-04', 'country1','area1', 'city2', 17
INSERT INTO rainfall SELECT '2016-01-05', 'country1','area1', 'city2', 18
INSERT INTO rainfall SELECT '2016-01-01', 'country1','area2', 'city3', 20
INSERT INTO rainfall SELECT '2016-01-02', 'country1','area2', 'city3', 22
INSERT INTO rainfall SELECT '2016-01-03', 'country1','area2', 'city3', 23
INSERT INTO rainfall SELECT '2016-01-04', 'country1','area2', 'city3', 24
INSERT INTO rainfall SELECT '2016-01-05', 'country1','area2', 'city3', 25
SELECT raindate
,r.country
,r.area
,SUM(r.fall)
FROM rainfall AS r
GROUP BY GROUPING SETS(
( raindate ), ( raindate,country ), (raindate,country, area ), ( ))
run above query will get result follows:
2016-01-01 00:00:00 country1 area1 24.00
2016-01-01 00:00:00 country1 area2 20.00
2016-01-01 00:00:00 country1 NULL 44.00
2016-01-01 00:00:00 NULL NULL 44.00
2016-01-02 00:00:00 country1 area1 35.00
2016-01-02 00:00:00 country1 area2 22.00
2016-01-02 00:00:00 country1 NULL 57.00
2016-01-02 00:00:00 NULL NULL 57.00
2016-01-03 00:00:00 country1 area1 28.00
2016-01-03 00:00:00 country1 area2 23.00
2016-01-03 00:00:00 country1 NULL 51.00
2016-01-03 00:00:00 NULL NULL 51.00
2016-01-04 00:00:00 country1 area1 32.00
2016-01-04 00:00:00 country1 area2 24.00
2016-01-04 00:00:00 country1 NULL 56.00
2016-01-04 00:00:00 NULL NULL 56.00
2016-01-05 00:00:00 country1 area1 34.00
2016-01-05 00:00:00 country1 area2 25.00
2016-01-05 00:00:00 country1 NULL 59.00
2016-01-05 00:00:00 NULL NULL 59.00
NULL NULL NULL 267.00