oracle 同比和环比 怎么写

[小谷]

-- Create table
create table T_REGISTER_REPORT
(
  ID            VARCHAR2(16) not null,
  CITY          VARCHAR2(6),
  TOTAL         NUMBER(10),
  BUSINESS_TYPE VARCHAR2(4),
  DATE_TIME     VARCHAR2(20)
)
insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('535', '3703', 23, '04', '2012-04-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('536', '3713', 3, '04', '2012-04-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('537', '3713', 1, '04', '2011-04-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('538', '3703', 5, '01', '2011-04-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('517', '6321', 1000, '01', '2011-01-11');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('518', '6321', 1000, '01', '2011-01-12');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('519', '6321', 1000, '01', '2011-01-19');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('520', '6321', 100, '01', '2011-02-14');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('521', '6321', 10, '01', '2011-08-12');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('522', '6321', 1, '01', '2011-08-19');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('523', '6321', 1, '01', '2011-09-12');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('524', '6321', 10, '01', '2011-12-12');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('525', '6321', 1, '01', '2011-12-14');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('526', '6321', 100, '01', '2012-03-01');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('527', '6321', 1000, '01', '2012-03-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('528', '1100', 3, '01', '2012-05-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('529', '1100', 1, '04', '2012-05-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('530', '3100', 2, '', '2012-05-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('531', '3100', 3, '01', '2012-05-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('532', '3100', 1, '04', '2012-05-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('533', '3703', 1, '', '2012-05-22');

insert into t_register_report (ID, CITY, TOTAL, BUSINESS_TYPE, DATE_TIME)
values ('534', '3713', 2, '04', '2012-05-22');





total   type     time           city
1000	01	2011-01-11	6321
1000	01	2011-01-12	6321
1000	01	2011-01-19	6321
100	01	2011-02-14	6321
1	04	2011-04-22	3713
5	01	2011-04-22	3703
10	01	2011-08-12	6321
1	01	2011-08-19	6321
1	01	2011-09-12	6321
10	01	2011-12-12	6321
1	01	2011-12-14	6321
100	01	2012-03-01	6321
1000	01	2012-03-22	6321
23	04	2012-04-22	3703
3	04	2012-04-22	3713
1	04	2012-05-22	3100
1		2012-05-22	3703
2	04	2012-05-22	3713
3	01	2012-05-22	3100
3	01	2012-05-22	1100
1	04	2012-05-22	1100
2		2012-05-22	3100

这些数据我要实现同比和环比 怎么写啊

[a494910091]

这样子可以否：

select city,
       business_type,
       date_time,
       total,
       LAG(total, 1, 0) OVER(partition by city, business_type order by date_time) as P_MONTE_TAL,
       LAG(total, 12, 0) OVER(partition by city, business_type order by date_time) as P_YEAR_MONTH_SAL
  from t_register_report t
 order by city, date_time;

[小谷]

[Quote=引用 1 楼 的回复:]

这样子可以否：
SQL code

select city,
business_type,
date_time,
total,
LAG(total, 1, 0) OVER(partition by city, business_type order by date_time) as P_MONTE_TAL,
LAG(tot……
[/Quote]

这个要求月份是连着的才行吧 我自己也这样试了 结果好像不正确

[Kobayashi]

楼上可行。。。

[forgetsam]

[Quote=引用 3 楼 的回复:]
这个要求月份是连着的才行吧 我自己也这样试了 结果好像不正确
[/Quote]

太简单了，那就先连接一个(select level n from dual connect by level<=12)，拿这个结果集再去做。