pig使用参考示例二

最新推荐文章于 2020-11-17 16:53:24 发布

jack-zhu

最新推荐文章于 2020-11-17 16:53:24 发布

阅读量1.2k

点赞数

分类专栏： pig 文章标签： pig使用参考示例二

本文链接：https://blog.csdn.net/zhu_xun/article/details/17226125

版权

pig 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

下面是我在做商家日结报表时的部分pig实现代码，该pig代码是根据SqlServer的存储过程改写而来的。

Sql Server的存储过程如下：

-- =============================================
-- Author: <jw>
-- Create date: <2013-08-19>
-- Description: <市级运营商结算报表 _e消费联盟商户结算表数据注入，按天计算>
-- exec [Sh_PB_ShopSettleMentNew] '2013-9-6'
--select * from Sh_TB_ShopSettleMent order by shopid
-- =============================================
ALTER procedure [dbo].[Sh_PB_ShopSettleMentNew](@NowDay date)
AS
BEGIN
set XACT_ABORT ON
begin transaction
--declare @NowDay date--当前日期
-- --set @NowDay=GETDATE()-1
-- set @NowDay='2013-5-23'
--select @NowDay
delete from Sh_TB_ShopSettleMent where PayTime=@NowDay
-- 消费产生的服务费和收益(1)
select
b.ShopID ShopID/*商家编号*/
,0 Status/*消费状态（0：有消费数据；1：没有消费数据）*/
,sum(a.AllPrice) ConAmount/*消费总额（元）*/
,sum(a.ShopDiture) ServiceCharge/*商家服务费*/
,sum(a.Monetary) CashAmount/*现金消费额（元）*/
,sum(a.Integral) CityOpToShop/*消费者积分消费额（元）*/
,sum(c.ZYIntegral) PlatformAmount/*众赢平台收益(元)*/
,sum(c.CityIntegral) CityIncome/*本市级运营商收益（元）*/
,sum(c.AreaIntegral) AreaIncome/*区级运营商收益（元）*/
,sum(c.SerIncome) SerIncome/*服务商联盟收益（元）*/
,sum(c.ExpIncome) ExpIncome/*拓展商联盟收益（元）*/
,sum(c.AgIntegral) AgIncome/*e消费代理商收益（元）*/
,sum(c.ShopIntegral) ShopIncome/*商家联盟收益（元）*/
,sum(c.UserIntegral) ConsumeIncome/*消费者消费返利（元）*/
,sum(c.UserLucky) CityAwardPool/*大奖池收益（元）*/
into #temp1
from CR_TB_ConsumerRecord a
inner join CR_TB_ConsumerRecordIntegral c on c.CRID=a.CRID
inner join Sh_TE_ShopInfo b on b.ShopID=a.ShopID and a.BuyTime<ISNULL(b.RemoveDate,GETDATE())
where a.BuyTime>=@NowDay and a.BuyTime<DATEADD(day,1,@NowDay)
group by b.ShopID
--联盟收益
select a.ShopID,
SUM(a.ShopIntegral)UserIncome,/*本商家注册会员所得联盟总收益（元）*/
SUM(case when a.AreaID=LEFT(b.AreaID,6) then a.ShopIntegral else 0 end)ShopIncomeBenQu,/*本商家注册会员所得本区联盟收益（元）*/
SUM(case when a.AreaID<>LEFT(b.AreaID,6) then a.ShopIntegral else 0 end)ShopIncomeTaQu/*本商家注册会员所得他区联盟收益（元）*/
into #temp2
from CR_TB_ConsumerRecordIntegral a
inner join Sh_TE_ShopInfo b on b.ShopID=a.ShopID and a.BuyTime<ISNULL(b.RemoveDate,GETDATE())
where a.BuyTime>=@NowDay and a.BuyTime<DATEADD(day,1,@NowDay)
group by a.ShopID

--退货收益
select c.ShopID
,SUM(cast(ReturnMoney as decimal(18,2))) ReturnMoney/*退款金额*/
,SUM(cast(c.ShopDiture/(c.AllPrice/a.ReturnMoney)as decimal(18,2))) ReturnAmount/*退款服务费*/
,SUM(cast(b.ZYIntegral/(c.AllPrice/a.ReturnMoney)as decimal(18,2))) ZYReturnIncome/*众赢平台退款联盟收益（元）*/
,SUM(cast(b.AreaIntegral/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))AreaOpReturnIncome/*区级运营商退款联盟收益（元）*/
,SUM(cast(b.SerIncome/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))SerReturnIncome/*服务商退款联盟收益（元）*/
,SUM(cast(b.ExpIncome/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))ExpReturnIncome/*拓展商退款联盟收益（元）*/
,SUM(cast(b.AgIntegral/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))AgReturnIncome/*代理商退款联盟收益（元）*/
,SUM(cast(b.ShopIntegral/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))ShopReturnIncome/*商家退款联盟收益（元）*/
,SUM(cast(b.UserIntegral/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))ConsumeReturnIncome/*消费者退款联盟收益（元）*/
,SUM(cast(b.UserLucky/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))BigWinReturnIncome/*大奖池退款联盟收益（元）*/
,SUM(cast(a.ReturnMoney-ReturnAllMoney as decimal(18,2))) ReturnIntegral/*退款奖励（平台垫付）*/
into #temp3
from CR_TB_ConsumerReturnGoods a
inner join CR_TB_ConsumerRecordIntegral b on b.CRID=a.CRID
inner join CR_TB_ConsumerRecord c on c.CRID=b.CRID
inner join Sh_TE_ShopInfo d on d.ShopID=c.ShopID and a.SuccessTime<ISNULL(d.RemoveDate,GETDATE())
where a.SuccessTime>=@NowDay and a.SuccessTime<DATEADD(day,1,@NowDay)
group by c.ShopID

--退款本商家注册会员所得联盟收益
select d.ShopID
,SUM(cast(b.ShopIntegral/(c.AllPrice/a.ReturnMoney)as decimal(18,2)))ReturnIncome/*退款本商家注册会员所得联盟总收益（元）*/
,SUM(cast(case when b.AreaID=LEFT(d.AreaID,6) then b.ShopIntegral/(c.AllPrice/a.ReturnMoney) else 0 end as decimal(18,2)))ReShopIncomeBenQu/*退款本商家注册会员所得本区联盟收益（元）*/
,SUM(cast(case when b.AreaID<>LEFT(d.AreaID,6) then b.ShopIntegral/(c.AllPrice/a.ReturnMoney) else 0 end as decimal(18,2)))ReShopIncomeTaQu/*退款本商家注册会员所得本区联盟收益（元）*/
into #temp4
from CR_TB_ConsumerReturnGoods a
inner join CR_TB_ConsumerRecordIntegral b on b.CRID=a.CRID
inner join CR_TB_ConsumerRecord c on c.CRID=b.CRID
inner join Sh_TE_ShopInfo d on d.ShopID=b.ShopID and a.SuccessTime<ISNULL(d.RemoveDate,GETDATE())
where a.SuccessTime>=@NowDay and a.SuccessTime<DATEADD(day,1,@NowDay)
group by d.ShopID

select distinct ShopID into #ShopID
from(
select ShopID from #temp1 union all select ShopID from #temp2
union all select ShopID from #temp3 union all select ShopID from #temp4
)a

insert into Sh_TB_ShopSettleMent(ShopID,PayTime,Status,ShopName,AreaID
,s2.ISCRatio,ConAmount,ServiceCharge,CashAmount,CityOpToShop,PlatformAmount,CityIncome,AreaIncome,SerIncome,ExpIncome,
AgIncome,ShopIncome,ConsumeIncome,CityAwardPool,UserIncome,ShopIncomeBenQu,ShopIncomeTaQu,ReturnIncome,ReShopIncomeBenQu,
ReShopIncomeTaQu,ReturnMoney,ReturnAmount,ZYReturnIncome,AreaOpReturnIncome,SerReturnIncome,ExpReturnIncome,AgReturnIncome,
ShopReturnIncome,ConsumeReturnIncome,BigWinReturnIncome,ReturnIntegral,CityOpID,AreaOpID,AreaOpName)
select s1.ShopID,@NowDay PayTime,isnull(a.Status,1),s1.ShortName ShopName,left(s1.AreaID,6)AreaID,s2.ISCRatio
,isnull(a.ConAmount,0),isnull(a.ServiceCharge,0),isnull(a.CashAmount,0),isnull(a.CityOpToShop,0),isnull(a.PlatformAmount,0),isnull(a.CityIncome,0),isnull(a.AreaIncome,0),isnull(a.SerIncome,0),isnull(a.ExpIncome,0)
,isnull(a.AgIncome,0),isnull(a.ShopIncome,0),isnull(a.ConsumeIncome,0),isnull(a.CityAwardPool,0),isnull(b.UserIncome,0),isnull(ShopIncomeBenQu,0),isnull(ShopIncomeTaQu,0),isnull(ReturnIncome,0),isnull(ReShopIncomeBenQu,0)
,isnull(ReShopIncomeTaQu,0),isnull(ReturnMoney,0),isnull(ReturnAmount,0),isnull(ZYReturnIncome,0),isnull(AreaOpReturnIncome,0),isnull(SerReturnIncome,0),isnull(ExpReturnIncome,0),isnull(AgReturnIncome,0)
,isnull(ShopReturnIncome,0),isnull(ConsumeReturnIncome,0),isnull(BigWinReturnIncome,0),isnull(ReturnIntegral,0),s4.OpID CityOpID,s3.OpID AreaOpID,s3.OpName AreaOpName
from #ShopID s
inner join Sh_TE_ShopInfo s1 on s1.ShopID=s.ShopID
inner join Sh_TE_ShopInformationService s2 on s2.ShopID=s.ShopID
inner join Op_TE_OperatorInfo s3 on s3.AreaID=left(s1.AreaID,6) and s3.Level=3
left join Op_TE_OperatorInfo s4 on s4.AreaID=left(s1.AreaID,4) and s4.Level=2
left join #temp1 a on a.ShopID=s.ShopID
left join #temp2 b on b.ShopID=s.ShopID
left join #temp3 c on c.ShopID=s.ShopID
left join #temp4 d on d.ShopID=s.ShopID

drop table #temp1;
drop table #temp2;
drop table #temp3;
drop table #temp4;
drop table #ShopID;

COMMIT transaction/*结束事务处理*/
set XACT_ABORT OFF

END
日结数据需求：
1.每天一次统计每个商家的服务费、收益、联盟收益、退货收益、退款本商家注册会员所得联盟收益
最终报表数据可以查询这个表：
select * from Sh_TB_ShopSettleMent where PayTime='2012-09-07';

2.具体所需的信息如下:
市级运营商编号、所属区级运营商编号、所属区级运营商名称、商家编号、商家名称、结算时间、区域编号、商家服务费率、消费总额（元）、商家服务费、现金消费额（元）、消费者积分消费额（元）、
众赢平台收益(元)、本市级运营商收益（元）、区级运营商收益（元）、服务商联盟收益（元）、拓展商联盟收益（元）、e消费代理商收益（元）
、商家联盟收益（元）、消费者消费返利（元）、大奖池收益（元）、本商家注册会员所得联盟总收益（元）、本商家注册会员所得本区联盟收益（元）
、本商家注册会员所得他区联盟收益（元）、退款本商家注册会员所得联盟总收益（元）、退款本商家注册会员所得本区联盟收益（元）、
退款本商家注册会员所得他区联盟收益（元）、退款金额、退款服务费、众赢平台退款联盟收益（元）、区级运营商退款联盟收益（元）、
服务商退款联盟收益（元）、拓展商退款联盟收益（元）、代理商退款联盟收益（元）、商家退款联盟收益（元）、消费者退款联盟收益（元）、
大奖池退款联盟收益（元）、退款奖励（平台垫付）

3.商家表：Sh_TE_ShopInfo （ShopID 运营商编号、ShortName 商家名称、AreaID 区域id 和运营商表的areaid对应）
4.运营商表：Op_TE_OperatorInfo 其中市级运营商编号取AreaID的前四位就可以了，区级运营商编号去areaid的前6位
5.消费总额（元）、商家服务费、现金消费额（元）、消费者积分消费额（元）可以从这个表取数据：CR_TB_ConsumerRecord
6.众赢平台收益(元)、本市级运营商收益（元）、区级运营商收益（元）、服务商联盟收益（元）、拓展商联盟收益（元）、e消费代理商收益（元）、
商家联盟收益（元）、消费者消费返利（元）、大奖池收益（元）可以从这个表取数据：CR_TB_ConsumerRecordIntegral
7.本商家注册会员所得联盟总收益（SUM(ShopIntegral)）、本商家注册会员所得本区联盟收益（SUM(case when a.AreaID=LEFT(b.AreaID,6) then a.ShopIntegral else 0 end)）、
本商家注册会员所得他区联盟收益（ SUM(case when a.AreaID<>LEFT(b.AreaID,6) then a.ShopIntegral else 0 end)）可以从CR_TB_ConsumerRecordIntegral取

8.退款金额：CR_TB_ConsumerReturnGoods表的ReturnMoney
9.退款服务费：CR_TB_ConsumerRecord表的AllPrice/CR_TB_ConsumerReturnGoods表的ReturnMoney
9.众赢平台退款联盟收益（ZYIntegral）、区级运营商退款联盟收益（AreaIntegral）、服务商退款联盟收益（SerIncome）、拓展商退款联盟收益（ExpIncome）、
代理商退款联盟收益（AgIntegral）
、商家退款联盟收益（ShopIntegral）、消费者退款联盟收益（UserIntegral）、大奖池退款联盟收益（UserLucky）：可以从CR_TB_ConsumerRecordIntegral表取数据
退款奖励（平台垫付）：SUM(cast(a.ReturnMoney-ReturnAllMoney as decimal(18,2))) ReturnIntegral
10.退款本商家注册会员所得联盟收益

对应的pig代码如下：