【 TiDB 使用环境】生产环境
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】sql如下图
with
op0 as (
select
substring_index(instrument_code, ‘', 1) as base_asset,
substring_index(instrument_code, '’,-1) as quote_asset,
from_unixtime(check_point / 1000, ‘%Y-%m-%d’) as event_date,
close as price
from
aloy.aloy_candlestick_cointr a
where
trading_cate = ‘S’
and check_point >= 1686441600000
and check_point< 1686528000000
and instrument_code != ‘U_T’
),
op1 as (
select
from_unixtime(check_point / 1000, '%Y-%m-%d') as event_date,
1/close as price
from
aloy.aloy_candlestick_cointr a
where
check_point >= 1686441600000
and check_point< 1686528000000
),
op2 as (
select op0.event_date,op0.base_asset,op0.price * op1.price as price from op0 left join op1 on (op0.event_date=op1.event_date)
where op0.quote_asset=‘T’ and op0.base_asset not in (
select op0.base_asset from op0 where quote_asset=‘ut’
)
),
op3 as (
select
‘U’ as base_asset,
‘U’ as quote_asset,
from_unixtime(check_point / 1000, ‘%Y-%m-%d’) as event_date,
1 as price
from
aloy.aloy_candlestick_cointr a
where
trading_cate = ‘s’
and instrument_code=‘U_T’
and check_point >= 1686441600000
and check_point< 1686528000000
),
op as (
select op0.base_asset,op0.event_date,op0.price from op0 where op0.quote_asset=‘U’
union
select op1.base_asset,op1.event_date,op1.price from op1
union
select op2.base_asset,op2.event_date,op2.price from op2
union
select op3.base_asset,op3.event_date,op3.price from op3
),
s0 as (
select from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date,
a.asset,
sum(amount) as amount
from aloy_dws_user_asset_daily a
where a.check_point = 1686441600000
group by asset
),
c0 as (
select
s0.event_date,
round(sum(s0.amount * op.price),1) as value
from s0
left join op on (s0.event_date=op.event_date and s0.asset=op.base_asset)
group by s0.event_date
),
s2 as (
select
from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date,
round(sum(uvalue-mm_uvalue),1) as value
from aloy_dws_user_trade_daily a
left join aloy_dws_user b on (a.uid=b.uid)
where a.check_point >=1686441600000 and a.check_point < 1686528000000 and a.trading_cate=‘s’
group by event_date
),
s3 as (
select ’
from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date,
round(sum(uvalue-mm_uvalue),1) as value
from aloy_dws_user_trade_daily a
left join aloy_dws_user b on (a.uid=b.uid)
where a.check_point >=1686441600000 and a.check_point < 1686528000000 and trading_cate=‘PERPETUAL_USD’
group by event_date
),
s4 as (
select
from_unixtime(register_time/1000,‘%Y-%m-%d’) as event_date,
count(1) as value
from aloy_dws_user
where register_time >=1686441600000 and register_time < 1686528000000
group by event_date
),
s5 as (
select
from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,
count(1) as value
from aloy_dws_user
where register_time < 1686528000000
),
s6 as (
select
from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,
coalesce(t.value, d.default_value) as value
from
(select 0 as default_value from dual) d left join
(select count(1) as value from aloy_dws_user where type!=1 and kyc_level >=10 and kyc_level1_review_time < 1686528000000) t on 1=1
),
c6_1 as(
select
a.uid,
min(kyc_level1_review_time) first_kyc_time
from aloy_dws_user a
where type!=1
and a.kyc_level >=10
and kyc_level1_review_time < 1686528000000
group by a.uid
),
s6_1 as (
select
from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,
coalesce(t.value, d.default_value) as value
from
(select 0 as default_value from dual) d left join
(select count(1) as value from c6_1 where first_kyc_time >=1686441600000 and first_kyc_time< 1686528000000) t on 1=1
),
s7 as (
select HIGH_PRIORITY a.uid,
min(a.check_point) first_deposit_date,
max(a.check_point) as last_deposit_date
from aloy_dws_wallet_daily a
left join aloy_dws_user b on (a.uid=b.uid)
where b.type not in (1,2) and deposit_amount >0
and a.check_point < ‘1686528000000’
group by a.uid
),
c7 as (
select
event_date,
coalesce(t.value, d.default_value) as value
from
(select 0 as default_value from dual) d
left join
( select
from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,
count(distinct a.uid) as value
from aloy_dws_wallet_daily a
where deposit_amount >0 and a.check_point >=1686441600000 and a.check_point < 1686528000000
and a.uid in (select uid from s7 where first_deposit_date >=1686441600000 and first_deposit_date< 1686528000000)
group by event_date
) t on 1=1
),
s8 as (
select asset,
deposit_amount,
uid,
from_unixtime(check_point/1000,‘%Y-%m-%d’) as event_date
from aloy_dws_wallet_daily
where deposit_amount > 0 and check_point >=1686441600000 and check_point < 1686528000000
and uid in (
select uid from s7 where first_deposit_date >=1686441600000 and first_deposit_date< 1686528000000
)
),
c8 as (
select t.event_date,
coalesce(t.value, d.default_value) as value from
(select 0 as default_value from dual) d left join
(select s8.event_date,round(sum(s8.deposit_amount * op.price),1) as value from s8 left join op on (s8.event_date=op.event_date and s8.asset=op.base_asset) group by s8.event_date) t on 1=1
),
c9 as (
select HIGH_PRIORITY asset,deposit_amount,uid,from_unixtime(check_point/1000,‘%Y-%m-%d’) as event_date from aloy_dws_wallet_daily where deposit_amount > 0 and check_point >=1686441600000 and check_point < 1686528000000 and uid in (
select uid from s7 where first_deposit_date <1686441600000
)
),
c90 as (
select HIGH_PRIORITY
coalesce(t.value, d.default_value) as value from
(select 0 as default_value from dual) d left join
(select event_date,count(distinct c9.uid) as value from c9 group by event_date) t on 1=1
),
c91 as (
select
coalesce(t.value, d.default_value) as value from
(select 0 as default_value from dual) d left join
(select c9.event_date,round(sum(c9.deposit_amount * op.price),1) as value from c9 left join op on (c9.event_date=op.event_date and c9.asset=op.base_asset) group by c9.event_date) t on 1=1
),
s10 as (
select from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date ,count(distinct a.uid) as value from aloy_dws_wallet_daily a left join aloy_dws_user b on (a.uid=b.uid) where b.type not in (1,2) and withdraw_amount >0 and a.check_point >=1686441600000 and a.check_point < 1686528000000 group by event_date
),
s11 as (
select from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date,sum(withdraw_amount) as withdraw_amount,asset from aloy_dws_wallet_daily a left join aloy_dws_user b on (a.uid=b.uid) where a.asset_type in(0,1,2) and b.type not in (1,2) and withdraw_amount >0 and a.check_point >=1686441600000 and a.check_point < 1686528000000 group by asset,a.check_point
),
c11 as (
select s11.event_date,round(sum(s11.withdraw_amount * op.price),1) as value from s11 left join op on (s11.event_date=op.event_date and s11.asset=op.base_asset) group by s11.event_date
),
s12 as (
select from_unixtime(check_point/1000,‘%Y-%m-%d’) as event_date,count(distinct uid) as value from aloy_dws_user_trade_daily where check_point >=1686441600000 and check_point < 1686528000000 group by event_date
),
s13 as (
select from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date,count(distinct a.uid) as value from aloy_dws_user_trade_daily a where a.check_point >=1686441600000 and a.check_point < 1686528000000 and trading_cate = ‘s’ group by event_date
),
s14 as (
select from_unixtime(a.check_point/1000,‘%Y-%m-%d’) as event_date,count(distinct a.uid) as value from aloy_dws_user_trade_daily a where a.check_point >=1686441600000 and a.check_point < 1686528000000 and trading_cate = ‘PERPETUAL_USD’ group by event_date
),
s15 as (
select from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,count(distinct a.uid) as value from infix.login_log a left join aloy_dws_user b on (a.uid=b.uid) where b.type!=1 and unix_timestamp(login_date) >=1686441600000/1000 and unix_timestamp(login_date) < 1686528000000/1000
),
s16 as (
select from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,count(distinct a.uid) as value from infix.login_log a left join aloy_dws_user b on (a.uid=b.uid) where b.type!=1 and unix_timestamp(login_date) >=1686441600000/1000 and unix_timestamp(login_date) < 1686528000000/1000 and login_system=‘pc’
),
s17 as (
select ,from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,count(distinct a.uid) as value from infix.login_log a left join aloy_dws_user b on (a.uid=b.uid) where b.type!=1 and unix_timestamp(login_date) >=1686441600000/1000 and unix_timestamp(login_date) < 1686528000000/1000 and (login_system=‘iOS’ or login_system=‘Android’)
),
c1 as (
select from_unixtime(1686528000000/1000,‘%Y-%m-%d’) as event_date,((select value from c8) + (select value from c91) - (select value from c11)) as value
),
cx as (
select kpi_name,format(value,1,‘en_US’),turkey_name from c0
union
select kpi_name,format(value,1,‘en_US’),turkey_name from c1
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s2
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s3
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s4
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s6_1
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s5
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s6
union
select kpi_name,format(value,1,‘en_US’),turkey_name from c7
union
select kpi_name,format(value,1,‘en_US’),turkey_name from c8
union
select kpi_name,format(value,1,‘en_US’),turkey_name from c90
union
select kpi_name,format(value,1,‘en_US’),turkey_name from c91
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s10
union
select kpi_name,format(value,1,‘en_US’),turkey_name from c11
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s12
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s13
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s14
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s15
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s16
union
select kpi_name,format(value,1,‘en_US’),turkey_name from s17
)
select * from cx
以上是sql逻辑，其中
这个as临时表，放到整个sql里和单独查询结果不一致
最后结果和其中一个as的临时表结果不一致