| --! qt:dataset:src |
| -- group-by/order-by/aggregation functions |
| |
| select |
| iym, count(*), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1 |
| group by iym |
| order by iym asc |
| limit 5; |
| |
| select |
| iym, count(*), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1 |
| group by iym |
| order by iym desc |
| limit 5; |
| |
| -- same query as previous, with having clause |
| select |
| iym, count(*), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1 |
| group by iym |
| having max(idt) > interval '496 0:0:0' day to second |
| order by iym desc |
| limit 5; |
| |
| select |
| idt, count(*), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1 |
| group by idt |
| order by idt asc |
| limit 5; |
| |
| select |
| idt, count(*), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1 |
| group by idt |
| order by idt desc |
| limit 5; |
| |
| -- same query as previous, with having clause |
| select |
| idt, count(*), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1 |
| group by idt |
| having max(iym) < interval '496-0' year to month |
| order by idt desc |
| limit 5; |
| |
| select |
| count(iym), count(idt), min(key), max(key), min(iym), max(iym), min(idt), max(idt) |
| from ( |
| select |
| key, |
| interval_year_month(concat(key, '-1')) as iym, |
| interval_day_time(concat(key, ' 1:1:1')) as idt |
| from src) q1; |
| |