#!/bin/bash
star=date +'%Y-%m-%d %H:%M:%S'
echo $(date “+%Y-%m-%d %H:%M:%S”);
echo currency:$1;
currency=
1
;
e
c
h
o
"
1; echo "
1;echo"currency";
echo Platform:$2;
Platform=
2
;
e
c
h
o
"
2; echo "
2;echo"Platform";
echo Country:$3;
Country=
3
;
e
c
h
o
"
3; echo "
3;echo"Country";
echo Account:$4;
Account=
4
;
e
c
h
o
"
4; echo "
4;echo"Account";
echo SalesStaff:$5;
SalesStaff=
5
;
e
c
h
o
"
5; echo "
5;echo"SalesStaff";
echo type:$6;
type=
6
;
e
c
h
o
"
6; echo "
6;echo"type";
echo starttime:$7;
starttime=
7
;
e
c
h
o
"
7; echo "
7;echo"starttime";
echo endtime:$8;
endtime=
8
;
e
c
h
o
"
8; echo "
8;echo"endtime";
echo MarketingPromotion:$9;
MarketingPromotion=
9
;
e
c
h
o
"
9; echo "
9;echo"MarketingPromotion";
echo Warehouse:
10
;
W
a
r
e
h
o
u
s
e
=
{10}; Warehouse=
10;Warehouse={10};
echo “
W
a
r
e
h
o
u
s
e
"
;
e
c
h
o
b
u
y
e
r
c
o
u
n
t
r
y
:
Warehouse"; echo buyer_country:
Warehouse";echobuyercountry:{11};
buyer_country=
11
;
e
c
h
o
"
{11}; echo "
11;echo"buyer_country”;
sql1=“where currency=‘CNY’”
echo “
s
q
l
1
"
i
f
[
!
−
n
"
sql1" if [ ! -n "
sql1"if[!−n"currency” ];then
echo “$currency”;
echo “currency is null”;
else
echo “currency not NULL”;
sql1="where currency='$1'";
echo "$sql1";
fi
echo "$sql1"
sql=‘where 1=1’;
echo “
s
q
l
"
;
s
q
l
2
=
"
w
h
e
r
e
1
=
1
a
n
d
T
r
a
n
s
a
c
t
i
o
n
T
y
p
e
s
=
′
付
款
′
"
;
e
c
h
o
"
sql"; sql2="where 1=1 and TransactionTypes='付款' "; echo "
sql";sql2="where1=1andTransactionTypes=′付款′";echo"sql2”;
sql3=“where 1=1”;
sql4=“and 1=1”;
if [ ! -n “
P
l
a
t
f
o
r
m
"
]
;
t
h
e
n
e
c
h
o
"
Platform" ];then echo "
Platform"];thenecho"Platform”;
echo “Platform is null”;
else
echo “Platform not NULL”;
sql="$sql and Platform in
2
"
;
s
q
l
2
=
"
2"; sql2="
2";sql2="sql2 and Platform in
2
"
;
s
q
l
3
=
"
2"; sql3="
2";sql3="sql3 and Platform in
2
"
;
s
q
l
4
=
"
2"; sql4="
2";sql4="sql4 and Platform in
2
"
;
f
i
e
c
h
o
"
2"; fi echo "
2";fiecho"sql";
echo “
s
q
l
2
"
;
e
c
h
o
"
sql2"; echo "
sql2";echo"sql3”;
echo “$sql4”;
if [ ! -n “
C
o
u
n
t
r
y
"
]
;
t
h
e
n
e
c
h
o
"
Country" ];then echo "
Country"];thenecho"Country”
echo “Country is null”
else
echo “Country not NULL”
sql="$sql and Country in
3
"
s
q
l
2
=
"
3" sql2="
3"sql2="sql2 and Country in
3
"
s
q
l
3
=
"
3" sql3="
3"sql3="sql3 and Country in
3
"
s
q
l
4
=
"
3" sql4="
3"sql4="sql4 and Country in
3
"
f
i
e
c
h
o
"
3" fi echo "
3"fiecho"sql"
echo “
s
q
l
2
"
e
c
h
o
"
sql2" echo "
sql2"echo"sql3”
echo “$sql4”;
if [ ! -n “
A
c
c
o
u
n
t
"
]
;
t
h
e
n
e
c
h
o
"
Account" ];then echo "
Account"];thenecho"Account”
echo “Account is null”
else
echo “Account not NULL”
sql="$sql and Account in
4
"
s
q
l
2
=
"
4" sql2="
4"sql2="sql2 and Account in
4
"
s
q
l
3
=
"
4" sql3="
4"sql3="sql3 and Account in
4
"
s
q
l
4
=
"
4" sql4="
4"sql4="sql4 and Account in
4
"
f
i
e
c
h
o
"
4" fi echo "
4"fiecho"sql"
echo “
s
q
l
2
"
e
c
h
o
"
sql2" echo "
sql2"echo"sql3”
echo “$sql4”;
if [ ! -n “
S
a
l
e
s
S
t
a
f
f
"
]
;
t
h
e
n
e
c
h
o
"
SalesStaff" ];then echo "
SalesStaff"];thenecho"SalesStaff”
echo “SalesStaff is null”
else
echo “SalesStaff not NULL”
sql="$sql and SalesStaff in
5
"
s
q
l
2
=
"
5" sql2="
5"sql2="sql2 and SalesStaff in
5
"
s
q
l
3
=
"
5" sql3="
5"sql3="sql3 and SalesStaff in
5
"
f
i
e
c
h
o
"
5" fi echo "
5"fiecho"sql"
echo “
s
q
l
2
"
e
c
h
o
"
sql2" echo "
sql2"echo"sql3”
echo “$sql4”;
if [ ! -n “
t
y
p
e
"
]
;
t
h
e
n
e
c
h
o
"
type" ];then echo "
type"];thenecho"type”
echo “type is null”
sql=“
s
q
l
a
n
d
t
y
p
e
=
1
"
s
q
l
4
=
"
sql and type = 1" sql4="
sqlandtype=1"sql4="sql4 and type = 1”
else
echo “type not NULL”
sql="$sql and type =
6
"
s
q
l
4
=
"
6" sql4="
6"sql4="sql4 and type =
6
"
f
i
e
c
h
o
"
6" fi echo "
6"fiecho"sql"
echo “
s
q
l
3
"
e
c
h
o
"
sql3" echo "
sql3"echo"sql4”
if [ ! -n “
s
t
a
r
t
t
i
m
e
"
]
;
t
h
e
n
e
c
h
o
"
starttime" ];then echo "
starttime"];thenecho"starttime”
echo “starttime is null”
else
echo “starttime not NULL”
sql="$sql and day >= toDate(toDateTime('
7
′
)
)
"
s
q
l
2
=
"
7'))" sql2="
7′))"sql2="sql2 and SettlementTime >= toDate(toDateTime(‘
7
′
)
)
"
s
q
l
3
=
"
7'))" sql3="
7′))"sql3="sql3 and SettlementTime >= toDate(toDateTime(‘
7
′
)
)
"
s
q
l
4
=
"
7'))" sql4="
7′))"sql4="sql4 and day >= toDate(toDateTime(’$7’))"
fi
echo "$sql"
echo "$sql2"
echo "$sql3"
echo "$sql4";
if [ ! -n “
e
n
d
t
i
m
e
"
]
;
t
h
e
n
e
c
h
o
"
endtime" ];then echo "
endtime"];thenecho"endtime”
echo “endtime is null”
else
echo “endtime not NULL”
sql="$sql and day <= toDate(toDateTime('
8
′
)
)
"
s
q
l
2
=
"
8'))" sql2="
8′))"sql2="sql2 and SettlementTime <= toDate(toDateTime('
8
′
)
)
"
s
q
l
3
=
"
8'))" sql3="
8′))"sql3="sql3 and SettlementTime <= toDate(toDateTime('
8
′
)
)
"
s
q
l
4
=
"
8'))" sql4="
8′))"sql4="sql4 and day <= toDate(toDateTime('
8
′
)
)
"
f
i
e
c
h
o
"
8'))" fi echo "
8′))"fiecho"sql"
echo “
s
q
l
2
"
e
c
h
o
"
sql2" echo "
sql2"echo"sql3”
echo “$sql4”;
if [ ! -n “
M
a
r
k
e
t
i
n
g
P
r
o
m
o
t
i
o
n
"
]
;
t
h
e
n
e
c
h
o
"
MarketingPromotion" ];then echo "
MarketingPromotion"];thenecho"MarketingPromotion”
echo “MarketingPromotion is null”
else
echo “MarketingPromotion not NULL”
sql="$sql and MarketingPromotion ='
9
′
"
s
q
l
2
=
"
9'" sql2="
9′"sql2="sql2 and MarketingPromotion =‘
9
′
"
s
q
l
3
=
"
9'" sql3="
9′"sql3="sql3 and MarketingPromotion =‘
9
′
"
s
q
l
4
=
"
9'" sql4="
9′"sql4="sql4 and MarketingPromotion =’$9’"
fi
echo "$sql"
echo "$sql2"
echo "$sql3"
echo "$sql4";
if [ ! -n “
W
a
r
e
h
o
u
s
e
"
]
;
t
h
e
n
e
c
h
o
"
Warehouse" ];then echo "
Warehouse"];thenecho"Warehouse”
echo “Warehouse is null”
else
echo “Warehouse not NULL”
sql="$sql and Warehouse in
10
"
s
q
l
2
=
"
{10}" sql2="
10"sql2="sql2 and Warehouse in
10
"
s
q
l
3
=
"
{10}" sql3="
10"sql3="sql3 and Warehouse in
10
"
s
q
l
4
=
"
{10}" sql4="
10"sql4="sql4 and Warehouse in ${10}"
fi
echo "$sql"
echo "$sql2"
echo "$sql3"
echo "$sql4";
if [ ! -n “
b
u
y
e
r
c
o
u
n
t
r
y
"
]
;
t
h
e
n
e
c
h
o
"
buyer_country" ];then echo "
buyercountry"];thenecho"buyer_country”
echo “buyer_country is null”
else
echo “buyer_country not NULL”
sql="$sql and buyer_country in
11
"
s
q
l
2
=
"
{11}" sql2="
11"sql2="sql2 and buyer_country in
11
"
s
q
l
3
=
"
{11}" sql3="
11"sql3="sql3 and buyer_country in
11
"
s
q
l
4
=
"
{11}" sql4="
11"sql4="sql4 and buyer_country in ${11}"
fi
echo “
s
q
l
"
;
e
c
h
o
"
sql"; echo "
sql";echo"sql1”
echo “
s
q
l
2
"
e
c
h
o
"
sql2" echo "
sql2"echo"sql3”;
echo “$sql4”;
if [ ! -n “$Country” ];then
echo “$sql2”
echo “$Country”
echo “Country is null”
sql2="$sql2"
echo “$sql2”
else
echo “Country not NULL”
echo “$sql2”;
sql2="$sql2 and Country in $3"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$Account” ];then
echo “$sql2”
echo “$Account”
echo “Account is null”
sql2="$sql2"
echo “$sql2”
else
echo “Account not NULL”
echo “$sql2”;
sql2="$sql2 and Account in $4"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$SalesStaff” ];then
echo “$sql2”
echo “$SalesStaff”
echo “SalesStaff is null”
sql2="$sql2"
echo “$sql2”
else
echo “SalesStaff not NULL”
echo “$sql2”;
sql2="$sql2 and SalesStaff in $5"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$starttime” ];then
echo “$sql2”
echo “$starttime”
echo “starttime is null”
sql2="$sql2"
echo “$sql2”
else
echo “starttime not NULL”
echo “$sql2”;
sql2="$sql2 and SettlementTime >= toDate(toDateTime(’$7’))"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$endtime” ];then
echo “$sql2”
echo “$endtime”
echo “endtime is null”
sql2="$sql2"
echo “$sql2”
else
echo “endtime not NULL”
echo “$sql2”;
sql2="$sql2 and SettlementTime <= toDate(toDateTime(’$8’))"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$MarketingPromotion” ];then
echo “$sql2”
echo “$MarketingPromotion”
echo “MarketingPromotion is null”
sql2="$sql2"
echo “$sql2”
else
echo “MarketingPromotion not NULL”
echo “$sql2”;
sql2="$sql2 and MarketingPromotion =’$9’"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$Warehouse” ];then
echo “$sql2”
echo “$Warehouse”
echo “Warehouse is null”
sql2="$sql2"
echo “$sql2”
else
echo “Warehouse not NULL”
echo “$sql2”;
sql2="$sql2 and Warehouse in ${10}"
echo “$sql2”;
fi
echo “$sql2”
if [ ! -n “$buyer_country” ];then
echo “$sql2”
echo “$buyer_country”
echo “buyer_country is null”
sql2="$sql2"
echo “$sql2”
else
echo “buyer_country not NULL”
echo “$sql2”;
sql2="$sql2 and buyer_country in ${11}"
echo “$sql2”;
fi
echo “$sql”;
echo “$sql1”;
echo “$sql2”;
echo “销售人员计算”;
clickhouse-client -h localhost --port 9000 -u default -d ‘finance_new_result’ -q “CREATE TABLE if not EXISTS finance_new_result.xiaoshou_result(
SalesStaff Nullable(String) COMMENT ‘销售人员’,
number Int64 COMMENT ‘总订单数’,
GrossProfit Decimal(10,4) COMMENT ‘总毛利润’,
SalesRevenue Decimal(10,4) COMMENT ‘总销售额’,
GrossProfit_rating String COMMENT ‘毛利润率’,
RefundAmount Decimal(10,4) COMMENT ‘退款总额’,
RefundAmount_rating String COMMENT ‘退款率’
)
ENGINE = MergeTree()
ORDER BY (
number,
GrossProfit,
SalesRevenue,
GrossProfit_rating,
RefundAmount,
RefundAmount_rating,
)”;
clickhouse-client -h localhost --port 9000 -u default --query=“truncate table finance_new_result.xiaoshou_result”;
clickhouse-client -h localhost --port 9000 -u default --query=“insert into finance_new_result.xiaoshou_result
select SalesStaff,number,GrossProfit,SalesRevenue,concat(toString(GrossProfit_rating),’%’) GrossProfit_rating,RefundAmount,
concat(toString(RefundAmount_rating),’%’) RefundAmount_rating
from (
select
a.SalesStaff SalesStaff,
c.number number,
intDivOrZero(a.GrossProfit,b.exchange_rate) GrossProfit,
intDivOrZero(a.SalesRevenue,b.exchange_rate) SalesRevenue,
toDecimal64(intDivOrZero(GrossProfit,SalesRevenue)*100,2) GrossProfit_rating,
intDivOrZero(a.RefundAmount,b.exchange_rate) RefundAmount,
toDecimal64(intDivOrZero(-RefundAmount,SalesRevenue-RefundAmount)*100,2) RefundAmount_rating
from
(select SalesStaff,
any(Country) Country1,any(Site) Site,
any(Settlement_Currency) Settlement_Currency,
sum(GrossProfit) GrossProfit,
sum(Revenue) Revenue,
sum(Expenses) Expenses,
sum(SalesRevenue) SalesRevenue,
sum(ShippingRevenue) ShippingRevenue,
sum(RefundAmount) RefundAmount,
sum(PreferentialAmount) PreferentialAmount,
sum(OtherIncome) OtherIncome,
sum(TransactionCommission) TransactionCommission,
sum(FbaOperationFee) FbaOperationFee,
sum(PlatformOtherExpenses) PlatformOtherExpenses,
sum(PurchaseCost) PurchaseCost,
sum(LocalWarehouseFreight) LocalWarehouseFreight,
sum(OverseasWarehouseFreight) OverseasWarehouseFreight,
sum(ForwardFreight) ForwardFreight,
sum(ExchangeRateDepreciation) ExchangeRateDepreciation,
sum(OtherExpenseCost) OtherExpenseCost,
sum(TaxRevenue) TaxRevenue
from finance_new_result.profit_salesstaff_day_result $sql
group by SalesStaff) a
left join
(select name,exchange_rate,currency,‘CNY’ use from finance_new.base_currencys $sql1) b
on a.Settlement_Currency = b.use
left join
(select count(1) number,any(SalesStaff1) SalesStaff from (
select PlatformOrderNumber,any(SalesStaff) SalesStaff1 from finance_new_result.profit_result
$sql2 group by PlatformOrderNumber
) group by SalesStaff1) c
on a.SalesStaff=c.SalesStaff
)”;
echo “店铺维度计算”;
clickhouse-client -hlocalhost --port 9000 -u default -d ‘finance_new_result’ -q “CREATE TABLE if not EXISTS finance_new_result.dianpu_result(
Account String COMMENT ‘账号’,
number Int64 COMMENT ‘总订单数’,
GrossProfit Decimal(10,4) COMMENT ‘总毛利润’,
SalesRevenue Decimal(10,4) COMMENT ‘总销售额’,
GrossProfit_rating String COMMENT ‘毛利润率’,
RefundAmount Decimal(10,4) COMMENT ‘退款总额’,
RefundAmount_rating String COMMENT ‘退款率’,
SalesStaff_rating Nullable(String) COMMENT ‘人员占比’
)
ENGINE = MergeTree()
ORDER BY (Account,
number,
GrossProfit,
SalesRevenue,
GrossProfit_rating,
RefundAmount,
RefundAmount_rating,
)”;
clickhouse-client -h localhost --port 9000 -u default --query=“truncate table finance_new_result.dianpu_result”;
clickhouse-client -h localhost --port 9000 -u default --query=“insert into finance_new_result.dianpu_result
select
Account,number,GrossProfit,SalesRevenue,GrossProfit_rating,RefundAmount,RefundAmount_rating, --arrayStringConcat(a.SalesStaff_rating) as test,
arrayStringConcat(SalesStaff_rating, ’ ‘) SalesStaff_rating
from (
select Account,number,GrossProfit,SalesRevenue,GrossProfit_rating,RefundAmount,RefundAmount_rating,
groupArray(SalesStaff_rating) SalesStaff_rating from
(
select Account,number,GrossProfit,SalesRevenue,concat(toString(GrossProfit_rating),’%’) GrossProfit_rating,
RefundAmount,concat(toString(RefundAmount_rating),’%’) RefundAmount_rating,concat(SalesStaff,’:’,concat(toString(SalesStaff_rating),’%’)) SalesStaff_rating
from (
select
a.Account Account,
c.number number,
intDivOrZero(a.GrossProfit,b.exchange_rate) GrossProfit,
intDivOrZero(a.SalesRevenue,b.exchange_rate) SalesRevenue,
toDecimal64(intDivOrZero(GrossProfit,SalesRevenue)*100,2) GrossProfit_rating,
intDivOrZero(a.RefundAmount,b.exchange_rate) RefundAmount,
toDecimal64(intDivOrZero(-RefundAmount,SalesRevenue-RefundAmount)*100,2) RefundAmount_rating,
d.SalesStaff SalesStaff,
toDecimal64(intDivOrZero(SalesRevenue,d.SalesRevenue1)*100,2) SalesStaff_rating
from
(select Account,
any(Country) Country1,
any(Site) Site,
any(Settlement_Currency) Settlement_Currency,
sum(GrossProfit) GrossProfit,
sum(Revenue) Revenue,
sum(Expenses) Expenses,
sum(SalesRevenue) SalesRevenue,
sum(ShippingRevenue) ShippingRevenue,
sum(RefundAmount) RefundAmount,
sum(PreferentialAmount) PreferentialAmount,
sum(OtherIncome) OtherIncome,
sum(TransactionCommission) TransactionCommission,
sum(FbaOperationFee) FbaOperationFee,
sum(PlatformOtherExpenses) PlatformOtherExpenses,
sum(PurchaseCost) PurchaseCost,
sum(LocalWarehouseFreight) LocalWarehouseFreight,
sum(OverseasWarehouseFreight) OverseasWarehouseFreight,
sum(ForwardFreight) ForwardFreight,
sum(ExchangeRateDepreciation) ExchangeRateDepreciation,
sum(OtherExpenseCost) OtherExpenseCost,
sum(TaxRevenue) TaxRevenue
from finance_new_result.profit_store_day_result where Account in
(select Account from finance_new_result.profit_result $sql3 group by Account)
$sql4 group by Account) a
left join
(select name,exchange_rate,currency,‘CNY’ use from finance_new.base_currencys $sql1) b
on a.Settlement_Currency = b.use
left join
(select count(1) number,any(Account1) Account from (
select PlatformOrderNumber,any(Account) Account1 from finance_new_result.profit_result
$sql2 group by PlatformOrderNumber
) group by Account1) c
on a.Account=c.Account
left join
(select a.Account Account,intDivOrZero(a.SalesRevenue,b.exchange_rate) SalesRevenue1,a.SalesStaff SalesStaff from
(select sum(SalesRevenue) SalesRevenue,Account,any(Settlement_Currency) Settlement_Currency,SalesStaff from finance_new_result.profit_salesstaff_day_result
$sql group by Account,SalesStaff) a
left join
(select name,exchange_rate,currency,‘CNY’ use from finance_new.base_currencys $sql1) b
on a.Settlement_Currency = b.use
)d
on a.Account=d.Account
)
) group by Account,number,GrossProfit,SalesRevenue,GrossProfit_rating,RefundAmount,RefundAmount_rating
)”;
echo “全部脚本完成”;
echo ( d a t e " + e n d = ‘ d a t e + ′ s t a r t s e c o n d s = (date "+%Y-%m-%d %H:%M:%S"); end=`date +'%Y-%m-%d %H:%M:%S'` start_seconds= (date"+end=‘date+′startseconds=(date --date=" s t a r " + e n d s e c o n d s = star" +%s); end_seconds= star"+endseconds=(date --date=" e n d " + e c h o " 本 次 运 行 时 间 ： " end" +%s); echo "本次运行时间： " end"+echo"本次运行时间："((end_seconds-start_seconds))“s”;
665
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
