mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-23 07:05:36 +03:00
53 lines
1.3 KiB
Plaintext
53 lines
1.3 KiB
Plaintext
USE tpch1;
|
|
drop table if exists paa;
|
|
drop table if exists dates;
|
|
drop table if exists t;
|
|
create table paa (
|
|
p_id int,
|
|
a_id int,
|
|
date_id int
|
|
) ENGINE=Columnstore DEFAULT CHARSET=latin1;
|
|
insert into paa values
|
|
(1, 10, 100),
|
|
(2, 20, 100);
|
|
create table dates (
|
|
date_id int,
|
|
dates date
|
|
) ENGINE=Columnstore DEFAULT CHARSET=latin1;
|
|
insert into dates values
|
|
(100, '2010-09-01');
|
|
create table t (
|
|
p_id int,
|
|
a_id int,
|
|
date_id int,
|
|
amount int
|
|
) ENGINE=Columnstore DEFAULT CHARSET=latin1;
|
|
insert into t values
|
|
(2, 20, 100, 99999);
|
|
select p.p_id,
|
|
p.a_id,
|
|
ifnull(`new_sales_amount`,0),
|
|
ifnull(`new_sales_amount_ok`,0),
|
|
p.dates as 'Date'
|
|
from (select aaa.p_id, aaa.a_id, dof.date_id, dof.dates
|
|
from (select paa.p_id, paa.a_id, 1 as xxx
|
|
from paa
|
|
) aaa
|
|
join (select date_id, 1 as xxx, dates from dates) dof on (dof.xxx=aaa.xxx)
|
|
) p
|
|
left outer join (select t.date_id, t.p_id, a_id,
|
|
IF(t.amount = 99999, amount, 0) / 10000 as 'new_sales_amount',
|
|
IF(t.amount = 99999, amount, 0) as 'new_sales_amount_ok'
|
|
from t
|
|
) tx
|
|
on (p.p_id = tx.p_id
|
|
and p.a_id = tx.a_id
|
|
and p.date_id = tx.date_id)
|
|
order by 1, 2, 3;
|
|
p_id a_id ifnull(`new_sales_amount`,0) ifnull(`new_sales_amount_ok`,0) Date
|
|
1 10 0.0000 0 2010-09-01
|
|
2 20 9.9999 99999 2010-09-01
|
|
drop table if exists paa;
|
|
drop table if exists dates;
|
|
drop table if exists t;
|