这个计算要处理时间,不太会

去化周期:当前月的存量/前六个月的平均成交量

标准差:以过去前36个月的去化周期做计算

下面是我处理其他数据的sql

select
A1.region_id,A1.city_id,
(A1.deal_qty-A4.deal_qty)/A4.deal_qty cjltb,--成交量同比
(A1.deal_qty-A3.deal_qty)/A3.deal_qty cjlhb,--成交量环比
(A1.aveprice_amt-A4.aveprice_amt)/A4.aveprice_amt jjtb,--均价同比
(A1.aveprice_amt-A3.aveprice_amt)/A3.aveprice_amt jjhb,--均价环比
(A1.act_deal-A4.act_deal)/A4.act_deal gqbtb,--供求比同比
(A1.act_deal-A3.act_deal)/A3.act_deal gqbhb,--供求比环比
(A1.stock_qty/A2.deal_qty_avg-A4.stock_qty/A6.deal_qty_avg)/A4.stock_qty/A6.deal_qty_avg qhzqtb,--去化周期同比
(A1.stock_qty/A2.deal_qty_avg-A3.stock_qty/A5.deal_qty_avg)/A3.stock_qty/A5.deal_qty_avg qhzqhb,--去化周期环比
(A1.sell_rate_pct-A4.sell_rate_pct)/A4.sell_rate_pct qhltb,--去化率同比
(A1.sell_rate_pct-A3.sell_rate_pct)/A3.sell_rate_pct qhlhb,--去化率环比
A1.deal_qty,--上月成交量
A1.aveprice_amt,--上月均价
A1.act_deal,--上月供求比
A1.stock_qty/A2.deal_qty_avg qhzq,--上月去化周期
A1.sell_rate_pct,--上月平均去化率
A7.deal_qty_p,--成交量平均数
A7.deal_qty_b,--成交量标准差
A7.aveprice_amt_p,--成交均价平均数
A7.aveprice_amt_b,--成交均价标准差
A7.act_deal_p,--供求比平均数
A7.act_deal_b--供求比标准差
from
(
select
region_id,city_id,
deal_qty,aveprice_amt,(case when deal_qty=0 then 0 else act_str/deal_qty end) as act_deal,sell_rate_pct,stock_qty
from dc_market_vol_city_f WHERE left(month_date,4)+'-'+right(month_date,2) = convert(varchar(7),DATEADD(mm, -1, GETDATE()),120)
--取上个月各城市量价存等数据
) A1
INNER JOIN
(
select
region_id,city_id,
SUM(deal_qty)/6 deal_qty_avg
from dc_market_vol_city_f
where
month_date >=  convert(varchar(7),DATEADD(mm, -7, GETDATE()),120)
and
month_date <= convert(varchar(7),DATEADD(mm, -1, GETDATE()),120)
GROUP BY region_id,city_id
--前六个月数据
) A2
ON A1.city_id = A2.city_id
LEFT JOIN
(
select
region_id,city_id,
deal_qty,aveprice_amt,(case when deal_qty=0 then 0 else act_str/deal_qty end) as act_deal,sell_rate_pct,stock_qty
from dc_market_vol_city_f WHERE left(month_date,4)+'-'+right(month_date,2) = convert(varchar(7),DATEADD(mm, -2, GETDATE()),120)
--取上上个月各城市量价存等数据，用于计算同环比
) A3
ON A1.city_id = A3.city_id
LEFT JOIN
(
select region_id,city_id,deal_qty,aveprice_amt,(case when deal_qty=0 then 0 else act_str/deal_qty end) as act_deal,sell_rate_pct,stock_qty
from dc_market_vol_city_f WHERE left(month_date,4)+'-'+right(month_date,2) = convert(varchar(7),DATEADD(mm, -13, GETDATE()),120)
--取去年上个月成交量数据，用于计算同比
) A4
ON A1.city_id = A4.city_id
 INNER JOIN
 (
 select
 region_id,city_id,
 SUM(deal_qty)/6 deal_qty_avg
 from dc_market_vol_city_f
 where
 month_date >=  convert(varchar(7),DATEADD(mm, -8, GETDATE()),120)
 and
 month_date <= convert(varchar(7),DATEADD(mm, -2, GETDATE()),120)
 GROUP BY region_id,city_id
--上个月前六个月数据
) A5
ON A1.city_id = A5.city_id
INNER JOIN
(
select
region_id,city_id,
SUM(deal_qty)/6 deal_qty_avg
from dc_market_vol_city_f
where
month_date >=  convert(varchar(7),DATEADD(mm, -19, GETDATE()),120)
and
month_date <= convert(varchar(7),DATEADD(mm, -13, GETDATE()),120)
GROUP BY region_id,city_id
--去年同期前六个月数据
) A6
ON A1.city_id = A6.city_id
INNER JOIN
(
select
region_id,city_id,
sum(deal_qty)/36 deal_qty_p,stdev(deal_qty) deal_qty_b,
sum(aveprice_amt)/36 aveprice_amt_p,stdev(aveprice_amt) aveprice_amt_b,
sum(act_str/deal_qty)/36 act_deal_p,stdev(act_str/deal_qty) act_deal_b
from dc_market_vol_city_f
where
month_date >=  convert(varchar(7),DATEADD(mm, -37, GETDATE()),120)
and
month_date <= convert(varchar(7),DATEADD(mm, -1, GETDATE()),120)
GROUP BY region_id,city_id
--前36个月数据
) A7
ON A1.city_id = A7.city_id
where A1.city_id = '320800'