【TPC-DS】DF的SQL（Data Maintenance部分）

对应的TPC-DS中的内容如下：

 

 

 重要：DF_CS中的删除catalog订单信息、删除catalog退单信息的between '1900-01-02' and '1901-02-01的时间要给定一致，因为他们属于一组，所以时间必须给定一致。（其他的同理）

==============执行的SQL如下==============

-- DF_CS
-- 删除catalog订单信息
delete from catalog_sales where cs_sold_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01');
-- 删除catalog退单信息
delete from catalog_returns where cr_order_number in (
select cs_order_number from catalog_sales where cs_sold_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01')
);

-- DF_SS
-- 删除store订单信息
delete from store_sales where ss_sold_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01');
-- 删除store退单信息
delete from store_returns where sr_ticket_number in (
select ss_ticket_number from store_sales where ss_sold_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01')
);

-- DF_WS
-- 删除web订单信息
delete from web_sales where ws_sold_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01');
-- 删除web退单信息
delete from web_returns where wr_order_number in (
select ws_order_number from web_sales where ws_sold_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01')
);

-- DF_I
-- 删除仓储
delete from inventory where inv_date_sk in (select d_date_sk from date_dim where d_date between '1900-01-02' and '1901-02-01');