582 lines
14 KiB
Plaintext
582 lines
14 KiB
Plaintext
CREATE TABLE t1(
|
|
product VARCHAR(32),
|
|
country_id INTEGER NOT NULL,
|
|
year INTEGER,
|
|
profit INTEGER);
|
|
INSERT INTO t1 VALUES ( 'Computer', 2,2000, 1200),
|
|
( 'TV', 1, 1999, 150),
|
|
( 'Calculator', 1, 1999,50),
|
|
( 'Computer', 1, 1999,1500),
|
|
( 'Computer', 1, 2000,1500),
|
|
( 'TV', 1, 2000, 150),
|
|
( 'TV', 2, 2000, 100),
|
|
( 'TV', 2, 2000, 100),
|
|
( 'Calculator', 1, 2000,75),
|
|
( 'Calculator', 2, 2000,75),
|
|
( 'TV', 1, 1999, 100),
|
|
( 'Computer', 1, 1999,1200),
|
|
( 'Computer', 2, 2000,1500),
|
|
( 'Calculator', 2, 2000,75),
|
|
( 'Phone', 3, 2003,10)
|
|
;
|
|
CREATE TABLE t2 (
|
|
country_id INTEGER PRIMARY KEY,
|
|
country CHAR(20) NOT NULL);
|
|
INSERT INTO t2 VALUES (1, 'USA'),(2,'India'), (3,'Finland');
|
|
# First simple rollups, with just grand total
|
|
|
|
SELECT product, SUM(profit) FROM t1 GROUP BY product;
|
|
product SUM(profit)
|
|
Calculator 275
|
|
Computer 6900
|
|
Phone 10
|
|
TV 600
|
|
|
|
SELECT product, SUM(profit) FROM t1 GROUP BY product WITH ROLLUP;
|
|
product SUM(profit)
|
|
NULL 7785
|
|
Calculator 275
|
|
Computer 6900
|
|
Phone 10
|
|
TV 600
|
|
|
|
SELECT product, SUM(profit) FROM t1 GROUP BY 1 WITH ROLLUP;
|
|
product SUM(profit)
|
|
NULL 7785
|
|
Calculator 275
|
|
Computer 6900
|
|
Phone 10
|
|
TV 600
|
|
|
|
SELECT product, SUM(profit),AVG(profit) FROM t1 GROUP BY product WITH ROLLUP;
|
|
product SUM(profit) AVG(profit)
|
|
NULL 7785 519.0000
|
|
Calculator 275 68.7500
|
|
Computer 6900 1380.0000
|
|
Phone 10 10.0000
|
|
TV 600 120.0000
|
|
|
|
# Sub totals
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year;
|
|
product country_id year SUM(profit)
|
|
Calculator 1 1999 50
|
|
Calculator 1 2000 75
|
|
Calculator 2 2000 150
|
|
Computer 1 1999 2700
|
|
Computer 1 2000 1500
|
|
Computer 2 2000 2700
|
|
Phone 3 2003 10
|
|
TV 1 1999 250
|
|
TV 1 2000 150
|
|
TV 2 2000 200
|
|
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP;
|
|
product country_id year SUM(profit)
|
|
NULL NULL NULL 7785
|
|
Calculator NULL NULL 275
|
|
Calculator 1 NULL 125
|
|
Calculator 1 1999 50
|
|
Calculator 1 2000 75
|
|
Calculator 2 NULL 150
|
|
Calculator 2 2000 150
|
|
Computer NULL NULL 6900
|
|
Computer 1 NULL 4200
|
|
Computer 1 1999 2700
|
|
Computer 1 2000 1500
|
|
Computer 2 NULL 2700
|
|
Computer 2 2000 2700
|
|
Phone NULL NULL 10
|
|
Phone 3 NULL 10
|
|
Phone 3 2003 10
|
|
TV NULL NULL 600
|
|
TV 1 NULL 400
|
|
TV 1 1999 250
|
|
TV 1 2000 150
|
|
TV 2 NULL 200
|
|
TV 2 2000 200
|
|
|
|
ANALYZE TABLE t1;
|
|
|
|
EXPLAIN FORMAT='plan_tree' SELECT product, country_id , year, SUM(profit)
|
|
FROM t1 GROUP BY product, country_id, year WITH ROLLUP;
|
|
id task access object operator info
|
|
Projection root Column, Column, Column, Column
|
|
└─HashAgg root group by:Column, Column, Column, Column, funcs:sum(Column)->Column, funcs:firstrow(Column)->Column, funcs:firstrow(Column)->Column, funcs:firstrow(Column)->Column
|
|
└─Projection root cast(executor__expand.t1.profit, decimal(10,0) BINARY)->Column, Column, Column, Column, gid->Column
|
|
└─Expand root level-projection:[executor__expand.t1.profit, <nil>->Column, <nil>->Column, <nil>->Column, 0->gid],[executor__expand.t1.profit, Column, <nil>->Column, <nil>->Column, 1->gid],[executor__expand.t1.profit, Column, Column, <nil>->Column, 3->gid],[executor__expand.t1.profit, Column, Column, Column, 7->gid]; schema: [executor__expand.t1.profit,Column,Column,Column,gid]
|
|
└─Projection root executor__expand.t1.profit, executor__expand.t1.product->Column, executor__expand.t1.country_id->Column, executor__expand.t1.year->Column
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false
|
|
|
|
SELECT product, country_id , SUM(profit) FROM t1
|
|
GROUP BY product, country_id WITH ROLLUP
|
|
ORDER BY product DESC, country_id;
|
|
product country_id SUM(profit)
|
|
TV NULL 600
|
|
TV 1 400
|
|
TV 2 200
|
|
Phone NULL 10
|
|
Phone 3 10
|
|
Computer NULL 6900
|
|
Computer 1 4200
|
|
Computer 2 2700
|
|
Calculator NULL 275
|
|
Calculator 1 125
|
|
Calculator 2 150
|
|
NULL NULL 7785
|
|
|
|
# limit
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP
|
|
ORDER BY product, country_id, year LIMIT 5;
|
|
product country_id year SUM(profit)
|
|
NULL NULL NULL 7785
|
|
Calculator NULL NULL 275
|
|
Calculator 1 NULL 125
|
|
Calculator 1 1999 50
|
|
Calculator 1 2000 75
|
|
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP
|
|
ORDER BY product, country_id, year limit 3,3;
|
|
product country_id year SUM(profit)
|
|
Calculator 1 1999 50
|
|
Calculator 1 2000 75
|
|
Calculator 2 NULL 150
|
|
|
|
SELECT product, country_id, COUNT(*), COUNT(distinct year)
|
|
FROM t1 GROUP BY product, country_id;
|
|
product country_id COUNT(*) COUNT(distinct year)
|
|
Calculator 1 2 2
|
|
Calculator 2 2 1
|
|
Computer 1 3 2
|
|
Computer 2 2 1
|
|
Phone 3 1 1
|
|
TV 1 3 2
|
|
TV 2 2 1
|
|
|
|
SELECT product, country_id, COUNT(*), COUNT(distinct year)
|
|
FROM t1 GROUP BY product, country_id WITH ROLLUP;
|
|
product country_id COUNT(*) COUNT(distinct year)
|
|
NULL NULL 15 3
|
|
Calculator NULL 4 2
|
|
Calculator 1 2 2
|
|
Calculator 2 2 1
|
|
Computer NULL 5 2
|
|
Computer 1 3 2
|
|
Computer 2 2 1
|
|
Phone NULL 1 1
|
|
Phone 3 1 1
|
|
TV NULL 5 2
|
|
TV 1 3 2
|
|
TV 2 2 1
|
|
|
|
# Test of having
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP HAVING country_id = 1;
|
|
product country_id year SUM(profit)
|
|
Calculator 1 NULL 125
|
|
Calculator 1 1999 50
|
|
Calculator 1 2000 75
|
|
Computer 1 NULL 4200
|
|
Computer 1 1999 2700
|
|
Computer 1 2000 1500
|
|
TV 1 NULL 400
|
|
TV 1 1999 250
|
|
TV 1 2000 150
|
|
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP HAVING SUM(profit) > 200;
|
|
product country_id year SUM(profit)
|
|
NULL NULL NULL 7785
|
|
Calculator NULL NULL 275
|
|
Computer NULL NULL 6900
|
|
Computer 1 NULL 4200
|
|
Computer 1 1999 2700
|
|
Computer 1 2000 1500
|
|
Computer 2 NULL 2700
|
|
Computer 2 2000 2700
|
|
TV NULL NULL 600
|
|
TV 1 NULL 400
|
|
TV 1 1999 250
|
|
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year WITH ROLLUP HAVING SUM(profit) > 7000;
|
|
product country_id year SUM(profit)
|
|
NULL NULL NULL 7785
|
|
|
|
# Functions
|
|
SELECT CONCAT(product,':',country_id) AS 'prod', CONCAT(":",year,":") AS 'year',
|
|
1+1, SUM(profit)/COUNT(*) FROM t1 GROUP BY 1,2 WITH ROLLUP;
|
|
prod year 1+1 SUM(profit)/COUNT(*)
|
|
NULL NULL 2 519.0000
|
|
Calculator:1 NULL 2 62.5000
|
|
Calculator:1 :1999: 2 50.0000
|
|
Calculator:1 :2000: 2 75.0000
|
|
Calculator:2 NULL 2 75.0000
|
|
Calculator:2 :2000: 2 75.0000
|
|
Computer:1 NULL 2 1400.0000
|
|
Computer:1 :1999: 2 1350.0000
|
|
Computer:1 :2000: 2 1500.0000
|
|
Computer:2 NULL 2 1350.0000
|
|
Computer:2 :2000: 2 1350.0000
|
|
Phone:3 NULL 2 10.0000
|
|
Phone:3 :2003: 2 10.0000
|
|
TV:1 NULL 2 133.3333
|
|
TV:1 :1999: 2 125.0000
|
|
TV:1 :2000: 2 150.0000
|
|
TV:2 NULL 2 100.0000
|
|
TV:2 :2000: 2 100.0000
|
|
|
|
SELECT product, SUM(profit)/COUNT(*) FROM t1 GROUP BY product WITH ROLLUP;
|
|
product SUM(profit)/COUNT(*)
|
|
NULL 519.0000
|
|
Calculator 68.7500
|
|
Computer 1380.0000
|
|
Phone 10.0000
|
|
TV 120.0000
|
|
|
|
SELECT LEFT(product,4) AS prod, SUM(profit)/COUNT(*) FROM t1
|
|
GROUP BY prod WITH ROLLUP;
|
|
prod SUM(profit)/COUNT(*)
|
|
NULL 519.0000
|
|
Calc 68.7500
|
|
Comp 1380.0000
|
|
Phon 10.0000
|
|
TV 120.0000
|
|
|
|
SELECT CONCAT(product,':',country_id), 1+1, SUM(profit)/COUNT(*) FROM t1
|
|
GROUP BY CONCAT(product,':',country_id) WITH ROLLUP;
|
|
CONCAT(product,':',country_id) 1+1 SUM(profit)/COUNT(*)
|
|
NULL 2 519.0000
|
|
Calculator:1 2 62.5000
|
|
Calculator:2 2 75.0000
|
|
Computer:1 2 1400.0000
|
|
Computer:2 2 1350.0000
|
|
Phone:3 2 10.0000
|
|
TV:1 2 133.3333
|
|
TV:2 2 100.0000
|
|
SET @saved_sql_mode = @@session.sql_mode;
|
|
SET SESSION sql_mode= '';
|
|
|
|
SELECT UPPER(product) AS prod,
|
|
SUM(profit)/COUNT(*)
|
|
FROM t1 GROUP BY prod WITH ROLLUP HAVING prod='COMPUTER' ;
|
|
prod SUM(profit)/COUNT(*)
|
|
COMPUTER 1380.0000
|
|
SET SESSION sql_mode= @saved_sql_mode;
|
|
|
|
# Joins
|
|
SELECT product, country , year, SUM(profit) FROM t1,t2 WHERE
|
|
t1.country_id=t2.country_id GROUP BY product, country, year WITH ROLLUP;
|
|
product country year SUM(profit)
|
|
NULL NULL NULL 7785
|
|
Calculator NULL NULL 275
|
|
Calculator India NULL 150
|
|
Calculator India 2000 150
|
|
Calculator USA NULL 125
|
|
Calculator USA 1999 50
|
|
Calculator USA 2000 75
|
|
Computer NULL NULL 6900
|
|
Computer India NULL 2700
|
|
Computer India 2000 2700
|
|
Computer USA NULL 4200
|
|
Computer USA 1999 2700
|
|
Computer USA 2000 1500
|
|
Phone NULL NULL 10
|
|
Phone Finland NULL 10
|
|
Phone Finland 2003 10
|
|
TV NULL NULL 600
|
|
TV India NULL 200
|
|
TV India 2000 200
|
|
TV USA NULL 400
|
|
TV USA 1999 250
|
|
TV USA 2000 150
|
|
|
|
SELECT product, `SUM` FROM (SELECT product, SUM(profit) AS 'sum' FROM t1
|
|
GROUP BY product WITH ROLLUP) AS tmp
|
|
WHERE product is null;
|
|
product SUM
|
|
NULL 7785
|
|
|
|
SELECT product FROM t1 WHERE EXISTS
|
|
(SELECT product, country_id , SUM(profit) FROM t1 AS t2
|
|
WHERE t1.product=t2.product GROUP BY product, country_id WITH ROLLUP
|
|
HAVING SUM(profit) > 6000);
|
|
product
|
|
Computer
|
|
Computer
|
|
Computer
|
|
Computer
|
|
Computer
|
|
|
|
SELECT product, country_id , year, SUM(profit) FROM t1
|
|
GROUP BY product, country_id, year HAVING country_id is NULL;
|
|
product country_id year SUM(profit)
|
|
|
|
SELECT CONCAT(':',product,':'), SUM(profit), AVG(profit) FROM t1
|
|
GROUP BY product WITH ROLLUP;
|
|
CONCAT(':',product,':') SUM(profit) AVG(profit)
|
|
NULL 7785 519.0000
|
|
:Calculator: 275 68.7500
|
|
:Computer: 6900 1380.0000
|
|
:Phone: 10 10.0000
|
|
:TV: 600 120.0000
|
|
|
|
drop table t1,t2;
|
|
CREATE TABLE t1 (i int);
|
|
INSERT INTO t1 VALUES(100);
|
|
CREATE TABLE t2 (i int);
|
|
INSERT INTO t2 VALUES (100),(200);
|
|
|
|
SELECT i, COUNT(*) FROM t1 GROUP BY i WITH ROLLUP;
|
|
i COUNT(*)
|
|
NULL 1
|
|
100 1
|
|
|
|
SELECT t1.i, t2.i, COUNT(*) FROM t1,t2 GROUP BY t1.i,t2.i WITH ROLLUP;
|
|
i i COUNT(*)
|
|
NULL NULL 2
|
|
100 NULL 2
|
|
100 100 1
|
|
100 200 1
|
|
|
|
DROP TABLE t1,t2;
|
|
CREATE TABLE user_day(
|
|
user_id INT NOT NULL,
|
|
date DATE NOT NULL,
|
|
UNIQUE INDEX user_date (user_id, date)
|
|
);
|
|
INSERT INTO user_day VALUES
|
|
(1, '2004-06-06' ),
|
|
(1, '2004-06-07' ),
|
|
(2, '2004-06-06' );
|
|
|
|
SELECT
|
|
d.date AS day,
|
|
COUNT(d.user_id) as sample,
|
|
COUNT(next_day.user_id) AS not_cancelled
|
|
FROM user_day d
|
|
LEFT JOIN user_day next_day
|
|
ON next_day.user_id=d.user_id AND
|
|
next_day.date= DATE_ADD( d.date, interval 1 day )
|
|
GROUP BY day;
|
|
day sample not_cancelled
|
|
2004-06-06 2 1
|
|
2004-06-07 1 0
|
|
|
|
SELECT
|
|
d.date AS day,
|
|
COUNT(d.user_id) as sample,
|
|
COUNT(next_day.user_id) AS not_cancelled
|
|
FROM user_day d
|
|
LEFT JOIN user_day next_day
|
|
ON next_day.user_id=d.user_id AND
|
|
next_day.date= DATE_ADD( d.date, interval 1 day )
|
|
GROUP BY day
|
|
WITH ROLLUP;
|
|
day sample not_cancelled
|
|
NULL 3 1
|
|
2004-06-06 2 1
|
|
2004-06-07 1 0
|
|
|
|
DROP TABLE user_day;
|
|
CREATE TABLE t1 (a int, b int);
|
|
INSERT INTO t1 VALUES
|
|
(1,4),
|
|
(2,2), (2,2),
|
|
(4,1), (4,1), (4,1), (4,1),
|
|
(2,1), (2,1);
|
|
|
|
SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b)
|
|
14
|
|
4
|
|
4
|
|
6
|
|
|
|
SELECT DISTINCT SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b)
|
|
14
|
|
4
|
|
6
|
|
|
|
SELECT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b) COUNT(DISTINCT b)
|
|
14 3
|
|
4 1
|
|
4 1
|
|
6 2
|
|
|
|
SELECT DISTINCT SUM(b), COUNT(DISTINCT b) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b) COUNT(DISTINCT b)
|
|
14 3
|
|
4 1
|
|
6 2
|
|
|
|
SELECT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b) COUNT(*)
|
|
14 9
|
|
4 1
|
|
4 4
|
|
6 4
|
|
|
|
SELECT DISTINCT SUM(b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b) COUNT(*)
|
|
14 9
|
|
4 1
|
|
4 4
|
|
6 4
|
|
|
|
SELECT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP;
|
|
SUM(b) COUNT(DISTINCT b) COUNT(*)
|
|
14 3 9
|
|
4 1 1
|
|
4 1 4
|
|
6 2 4
|
|
|
|
SELECT DISTINCT SUM(b), COUNT(DISTINCT b), COUNT(*) FROM t1
|
|
GROUP BY a WITH ROLLUP;
|
|
SUM(b) COUNT(DISTINCT b) COUNT(*)
|
|
14 3 9
|
|
4 1 1
|
|
4 1 4
|
|
6 2 4
|
|
|
|
SELECT a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
a SUM(b)
|
|
NULL 14
|
|
1 4
|
|
1 4
|
|
2 2
|
|
2 4
|
|
2 6
|
|
4 4
|
|
4 4
|
|
|
|
SELECT DISTINCT a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
a SUM(b)
|
|
NULL 14
|
|
1 4
|
|
2 2
|
|
2 4
|
|
2 6
|
|
4 4
|
|
|
|
SELECT b, a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
b a SUM(b)
|
|
NULL NULL 14
|
|
NULL 1 4
|
|
NULL 2 6
|
|
NULL 4 4
|
|
1 2 2
|
|
1 4 4
|
|
2 2 4
|
|
4 1 4
|
|
|
|
SELECT DISTINCT b,a, SUM(b) FROM t1 GROUP BY a,b WITH ROLLUP;
|
|
b a SUM(b)
|
|
NULL NULL 14
|
|
NULL 1 4
|
|
NULL 2 6
|
|
NULL 4 4
|
|
1 2 2
|
|
1 4 4
|
|
2 2 4
|
|
4 1 4
|
|
|
|
ALTER TABLE t1 ADD COLUMN c INT;
|
|
|
|
SELECT a,b,SUM(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
|
|
a b SUM(c)
|
|
NULL NULL NULL
|
|
1 NULL NULL
|
|
1 4 NULL
|
|
1 4 NULL
|
|
2 NULL NULL
|
|
2 1 NULL
|
|
2 1 NULL
|
|
2 2 NULL
|
|
2 2 NULL
|
|
4 NULL NULL
|
|
4 1 NULL
|
|
4 1 NULL
|
|
|
|
SELECT distinct a,b,SUM(c) FROM t1 GROUP BY a,b,c WITH ROLLUP;
|
|
a b SUM(c)
|
|
NULL NULL NULL
|
|
1 NULL NULL
|
|
1 4 NULL
|
|
2 NULL NULL
|
|
2 1 NULL
|
|
2 2 NULL
|
|
4 NULL NULL
|
|
4 1 NULL
|
|
DROP TABLE t1;
|
|
|
|
SELECT
|
|
(SELECT 100.00
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 1 MONTH)) AS 'PROFIT'
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
PROFIT
|
|
NULL
|
|
NULL
|
|
|
|
EXPLAIN SELECT
|
|
(SELECT 100.00
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 1 MONTH)) AS 'PROFIT'
|
|
FROM
|
|
(SELECT '2024-09-15' AS DATE) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
id estRows task access object operator info
|
|
Projection_18 1.00 root Column#6
|
|
└─Apply_22 1.00 root CARTESIAN left outer join, left side:HashAgg_23
|
|
├─HashAgg_23(Build) 1.00 root group by:Column#3, gid, funcs:firstrow(Column#1)->Column#1
|
|
│ └─Expand_31 1.00 root level-projection:[Column#1, <nil>->Column#3, 0->gid],[Column#1, Column#3, 1->gid]; schema: [Column#1,Column#3,gid]
|
|
│ └─Projection_34 1.00 root 2024-09-15->Column#1, 2024-09-15->Column#3
|
|
│ └─TableDual_36 1.00 root rows:1
|
|
└─Projection_40(Probe) 0.80 root 100.00->Column#6
|
|
└─Selection_42 0.80 root eq(Column#1, "2024-10-15")
|
|
└─TableDual_44 1.00 root rows:1
|
|
drop table if exists tr;
|
|
create table tr(a date);
|
|
insert into tr values('2024-09-15');
|
|
|
|
SELECT
|
|
(SELECT 100.00
|
|
FROM (SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 0 MONTH)
|
|
) AS 'PROFIT'
|
|
FROM (select tr.a as DATE from tr) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
PROFIT
|
|
100.00
|
|
100.00
|
|
|
|
EXPLAIN SELECT
|
|
(SELECT 100.00
|
|
FROM (SELECT '2024-09-15' AS DATE ) newTb
|
|
WHERE T0.DATE = DATE_ADD(newTb.DATE, INTERVAL 0 MONTH)
|
|
) AS 'PROFIT'
|
|
FROM (select tr.a as DATE from tr) T0
|
|
GROUP BY T0.DATE WITH ROLLUP;
|
|
id estRows task access object operator info
|
|
Projection_18 8000.00 root Column#7
|
|
└─Apply_22 8000.00 root CARTESIAN left outer join, left side:HashAgg_23
|
|
├─HashAgg_23(Build) 8000.00 root group by:Column#4, gid, funcs:firstrow(executor__expand.tr.a)->executor__expand.tr.a
|
|
│ └─Expand_31 10000.00 root level-projection:[executor__expand.tr.a, <nil>->Column#4, 0->gid],[executor__expand.tr.a, Column#4, 1->gid]; schema: [executor__expand.tr.a,Column#4,gid]
|
|
│ └─Projection_33 10000.00 root executor__expand.tr.a, executor__expand.tr.a->Column#4
|
|
│ └─TableReader_36 10000.00 root data:TableFullScan_35
|
|
│ └─TableFullScan_35 10000.00 cop[tikv] table:tr keep order:false, stats:pseudo
|
|
└─Projection_39(Probe) 6400.00 root 100.00->Column#7
|
|
└─Selection_41 6400.00 root eq(executor__expand.tr.a, 2024-09-15 00:00:00.000000)
|
|
└─TableDual_43 8000.00 root rows:1
|