By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
id | user_id | title | data |
---|---|---|---|
1 | 1 | DATE | 01-01-2021 |
2 | 1 | COLOR | RED |
3 | 2 | DATE | null |
4 | 2 | COLOR | YELLOW |
5 | 3 | DATE | 04-01-2021 |
6 | 3 | COLOR | RED |
5 | 4 | DATE | 04-01-2021 |
6 | 4 | COLOR | PINK |
7 | 5 | DATE | 02-01-2021 |
8 | 5 | COLOR | PINK |
7 | 6 | DATE | 08-01-2021 |
8 | 6 | COLOR | PINK |
3 | 7 | DATE | 07-07-2027 |
4 | 7 | COLOR | YELLOW |
z |
---|
2021-01-01 |
null |
2021-01-04 |
2021-01-04 |
2021-01-02 |
2021-01-08 |
2027-07-07 |
uid | color | c_date |
---|---|---|
1 | RED | 2021-01-01 |
2 | YELLOW | null |
3 | RED | 2021-01-04 |
4 | PINK | 2021-01-04 |
5 | PINK | 2021-01-02 |
6 | PINK | 2021-01-08 |
7 | YELLOW | 2027-07-07 |
uid | color | c_date |
---|---|---|
1 | RED | 2021-01-01 |
2 | YELLOW | null |
3 | RED | 2021-01-04 |
4 | PINK | 2021-01-04 |
5 | PINK | 2021-01-02 |
6 | PINK | 2021-01-08 |
7 | YELLOW | 2027-07-07 |
Color | Collected | Total |
---|---|---|
PINK | 3 | 3 |
RED | 2 | 2 |
YELLOW | 1 | 2 |
Color | Collected | Total |
---|---|---|
PINK | 3 | 3 |
RED | 2 | 2 |
YELLOW | 1 | 2 |
Color | Collected | Total |
---|---|---|
PINK | 3 | 3 |
RED | 2 | 2 |
YELLOW | 1 | 2 |
Color | Collected | Total |
---|---|---|
PINK | 3 | 3 |
RED | 2 | 2 |
YELLOW | 1 | 2 |
Query_ID | Duration | Query |
---|---|---|
1 | 0.00055400 | SELECT dt.color AS "Color", COUNT(dt.c_date) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color ORDER BY dt.color |
2 | 0.00045700 | SELECT dt.color AS "Color", SUM(CASE WHEN dt.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color ORDER BY dt.color |
3 | 0.00062175 | SELECT t2.data AS "Color", SUM(CASE WHEN t1.data IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) Total /*ROUND(SUM(CASE WHEN t1.data IS NOT NULL THEN 1.0 ELSE 0.0 END ) * 100 / COUNT(*), 2) AS "% collected"*/ FROM demo t1 JOIN demo t2 ON t1.user_ |
4 | 0.00070750 | SELECT v.color AS "Color", SUM(CASE WHEN v.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_view v GROUP BY v.color ORDER BY 1 |
EXPLAIN |
---|
-> Table scan on <temporary> (actual time=0.000..0.001 rows=3 loops=1) -> Aggregate using temporary table (actual time=0.048..0.049 rows=3 loops=1) -> Table scan on dt (cost=0.95 rows=7) (actual time=0.011..0.022 rows=7 loops=1) |
EXPLAIN |
---|
-> Table scan on <temporary> (actual time=0.001..0.001 rows=3 loops=1) -> Aggregate using temporary table (actual time=0.122..0.122 rows=3 loops=1) -> Inner hash join (y.user_id = x.user_id) (cost=3.32 rows=0) (actual time=0.050..0.081 rows=7 loops=1) -> Filter: (y.title = 'DATE') (cost=1.16 rows=1) (actual time=0.005..0.030 rows=7 loops=1) -> Table scan on y (cost=1.16 rows=14) (actual time=0.004..0.024 rows=14 loops=1) -> Hash -> Filter: (x.title = 'COLOR') (cost=1.65 rows=1) (actual time=0.017..0.032 rows=7 loops=1) -> Table scan on x (cost=1.65 rows=14) (actual time=0.012..0.027 rows=14 loops=1) |
EXPLAIN |
---|
-> Sort: t2.`data` (actual time=0.116..0.116 rows=3 loops=1) -> Table scan on <temporary> (actual time=0.001..0.001 rows=3 loops=1) -> Aggregate using temporary table (actual time=0.105..0.106 rows=3 loops=1) -> Inner hash join (t2.user_id = t1.user_id) (cost=3.32 rows=0) (actual time=0.048..0.075 rows=7 loops=1) -> Filter: (t2.title = 'COLOR') (cost=1.16 rows=1) (actual time=0.006..0.028 rows=7 loops=1) -> Table scan on t2 (cost=1.16 rows=14) (actual time=0.004..0.022 rows=14 loops=1) -> Hash -> Filter: (t1.title = 'DATE') (cost=1.65 rows=1) (actual time=0.013..0.031 rows=7 loops=1) -> Table scan on t1 (cost=1.65 rows=14) (actual time=0.012..0.026 rows=14 loops=1) |
EXPLAIN |
---|
-> Sort: dt.color (actual time=0.052..0.052 rows=3 loops=1) -> Table scan on <temporary> (actual time=0.001..0.001 rows=3 loops=1) -> Aggregate using temporary table (actual time=0.042..0.043 rows=3 loops=1) -> Table scan on dt (cost=0.95 rows=7) (actual time=0.011..0.021 rows=7 loops=1) |
user_id | title | data | w | x |
---|---|---|---|---|
1 | DATE | 01-01-2021 | 2021-01-01 | 2021-01-01 |
2 | DATE | null | null | null |
3 | DATE | 04-01-2021 | 2021-01-04 | 2021-01-04 |
4 | DATE | 04-01-2021 | 2021-01-04 | 2021-01-04 |
5 | DATE | 02-01-2021 | 2021-01-02 | 2021-01-02 |
6 | DATE | 08-01-2021 | 2021-01-08 | 2021-01-08 |
7 | DATE | 07-07-2027 | 2027-07-07 | 2027-07-07 |