start time: Tue Jun 7 18:21:50 CST 2022
tcmalloc: large alloc 1311277056 bytes == 0x11125e000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fb48b93dea5
tcmalloc: large alloc 2622390272 bytes == 0x15f4e6000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fb48b93dea5
tcmalloc: large alloc 5244649472 bytes == 0x1fb9ce000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fb48b93dea5
tcmalloc: large alloc 10489135104 bytes == 0x378b7e000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fb48b93dea5
tcmalloc: large alloc 2076770304 bytes == 0x1c721a000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fb48b93dea5
tcmalloc: large alloc 2281701376 bytes == 0x242eaa000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fb48b93dea5
start time: Tue Jun 7 18:35:11 CST 2022
*** Check failure stack trace: ***
@ 0x353a97d google::LogMessage::Fail()
@ 0x353cc59 google::LogMessage::SendToLog()
@ 0x353a4f9 google::LogMessage::Flush()
@ 0x353d259 google::LogMessageFatal::~LogMessageFatal()
@ 0x15ff357 main
@ 0x7f4a9ccaa555 __libc_start_main
@ 0x16eb6fe (unknown)
@ (nil) (unknown)
start time: Tue Jun 7 18:41:22 CST 2022
tcmalloc: large alloc 1073741824 bytes == 0x2a70d6000 @ 0x4ed9709 0x50573dc 0x505773e 0x1d1aed9 0x1d0115d 0x1cabfd3 0x1c6f907 0x2297ad8 0x229e963 0x229a054 0x228b5b6 0x2291ee0 0x23d5c90 0x2292013 0x23d5c90 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7f19dee2cea5
tcmalloc: large alloc 1699364864 bytes == 0x33e42e000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7f19dee2cea5
tcmalloc: large alloc 3398713344 bytes == 0x169a76000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7f19dee2cea5
tcmalloc: large alloc 6797352960 bytes == 0x45ae42000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7f19dee2cea5
tcmalloc: large alloc 2147483648 bytes == 0x5f9474000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7f19dee2cea5
tcmalloc: large alloc 13594607616 bytes == 0x679474000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7f19dee2cea5
terminate called after throwing an instance of ‘std::length_error’
what(): vector::_M_range_insert
*** Aborted at 1654603186 (unix time) try “date -d @1654603186” if you are using GNU date ***
PC: @ 0x7f19de169387 __GI_raise
*** SIGABRT (@0xd28f) received by PID 53903 (TID 0x7f1999578700) from PID 53903; stack trace: ***
@ 0x3544aa2 google::(anonymous namespace)::FailureSignalHandler()
@ 0x7f19dee34630 (unknown)
@ 0x7f19de169387 __GI_raise
@ 0x7f19de16aa78 __GI_abort
@ 0x15f9ba9 _ZN9__gnu_cxx27__verbose_terminate_handlerEv.cold
@ 0x4fa7726 __cxxabiv1::__terminate()
@ 0x4fa7791 std::terminate()
@ 0x4fa78e4 __cxa_throw
@ 0x15fb78d std::__throw_length_error()
@ 0x176ba62 std::vector<>::_M_range_insert<>()
@ 0x17661d4 starrocks::vectorized::BinaryColumn::append()
@ 0x20fe516 starrocks::vectorized::NullableColumn::append()
@ 0x239fe72 starrocks::vectorized::CrossJoinNode::_copy_joined_rows_with_index_base_probe()
@ 0x23a1329 starrocks::vectorized::CrossJoinNode::get_next_internal()
@ 0x23a1832 ZNSt17_Function_handlerIFN9starrocks6StatusEPNS0_12RuntimeStateEPSt10shared_ptrINS0_10vectorized5ChunkEEPbEZNS5_13CrossJoinNode8get_nextES3_S8_S9_EUlS3_S8_S9_E_E9_M_invokeERKSt9_Any_dataOS3_OS8_OS9
@ 0x21192c4 starrocks::ExecNode::get_next_big_chunk()
@ 0x239eb98 starrocks::vectorized::CrossJoinNode::get_next()
@ 0x23d3b81 starrocks::vectorized::ProjectNode::get_next()
@ 0x228c8cf starrocks::vectorized::HashJoinNode::_probe()
@ 0x228d511 starrocks::vectorized::HashJoinNode::get_next()
@ 0x23d3b81 starrocks::vectorized::ProjectNode::get_next()
@ 0x21f58f3 starrocks::vectorized::AggregateStreamingNode::get_next()
@ 0x1cd985a starrocks::PlanFragmentExecutor::_get_next_internal_vectorized()
@ 0x1cdb195 starrocks::PlanFragmentExecutor::_open_internal_vectorized()
@ 0x1cdbd67 starrocks::PlanFragmentExecutor::open()
@ 0x1c775a2 starrocks::FragmentExecState::execute()
@ 0x1c7bdbc starrocks::FragmentMgr::exec_actual()
@ 0x1c7c5e1 _ZNSt17_Function_handlerIFvvEZN9starrocks11FragmentMgr18exec_plan_fragmentERKNS1_23TExecPlanFragmentParamsERKSt8functionIFvPNS1_20PlanFragmentExecutorEEESC_EUlvE_E9_M_invokeERKSt9_Any_data
@ 0x1da2bd9 starrocks::ThreadPool::dispatch_thread()
@ 0x1d9e78a starrocks:
:supervise_thread()
@ 0x7f19dee2cea5 start_thread
@ 0x7f19de23196d __clone
start time: Tue Jun 7 20:05:43 CST 2022
start time: Tue Jun 7 20:09:13 CST 2022
tcmalloc: large alloc 1073741824 bytes == 0x460a42000 @ 0x4ed9709 0x50573dc 0x505773e 0x1d1aed9 0x1d0115d 0x1cabfd3 0x1c6f907 0x2297ad8 0x229e963 0x229a054 0x228b5b6 0x2291ee0 0x23d5c90 0x2292013 0x23d5c90 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
tcmalloc: large alloc 2018934784 bytes == 0x4f759c000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
tcmalloc: large alloc 4037722112 bytes == 0x3aa78c000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
tcmalloc: large alloc 8075419648 bytes == 0x5c403a000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
tcmalloc: large alloc 2148188160 bytes == 0x3aa78c000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
tcmalloc: large alloc 3580706816 bytes == 0x3aa78c000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
tcmalloc: large alloc 7161356288 bytes == 0x57603a000 @ 0x4ed9709 0x50573dc 0x1d14f6a 0x4fa7c75 0x17661d4 0x20fe516 0x20f4f42 0x23a1b17 0x23a2275 0x23d5c90 0x2292013 0x23d5c90 0x21fbd11 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fc4c8bc1ea5
start time: Tue Jun 7 20:50:48 CST 2022
*** Check failure stack trace: ***
@ 0x353a97d google::LogMessage::Fail()
@ 0x353cc59 google::LogMessage::SendToLog()
@ 0x353a4f9 google::LogMessage::Flush()
@ 0x353d259 google::LogMessageFatal::~LogMessageFatal()
@ 0x15ff357 main
@ 0x7f36d66d4555 __libc_start_main
@ 0x16eb6fe (unknown)
@ (nil) (unknown)
start time: Tue Jun 7 20:52:43 CST 2022
start time: Tue Jun 7 20:53:55 CST 2022
start time: Tue Jun 7 21:00:32 CST 2022
*** Check failure stack trace: ***
@ 0x353a97d google::LogMessage::Fail()
@ 0x353cc59 google::LogMessage::SendToLog()
@ 0x353a4f9 google::LogMessage::Flush()
@ 0x353d259 google::LogMessageFatal::~LogMessageFatal()
@ 0x15ff357 main
@ 0x7f769e642555 __libc_start_main
@ 0x16eb6fe (unknown)
@ (nil) (unknown)
start time: Tue Jun 7 21:02:42 CST 2022
tcmalloc: large alloc 1073741824 bytes == 0x48d9d4000 @ 0x4ed9709 0x50573dc 0x505773e 0x1d1aed9 0x1d0115d 0x1cabfd3 0x1c6f907 0x2297ad8 0x229e963 0x229a054 0x228b5b6 0x2291ee0 0x23d5c90 0x2292013 0x23d5c90 0x1cdb07b 0x1cdbd67 0x1c775a2 0x1c7bdbc 0x1c7c5e1 0x1da2bd9 0x1d9e78a 0x7fbeef1bfea5
terminate called after throwing an instance of ‘std::bad_variant_access’
what(): std::get: wrong index for variant
*** Aborted at 1654678700 (unix time) try “date -d @1654678700” if you are using GNU date ***
PC: @ 0x7fbeee4fc387 __GI_raise
*** SIGABRT (@0x5dee) received by PID 24046 (TID 0x7fbe1d739700) from PID 24046; stack trace: ***
@ 0x3544aa2 google::(anonymous namespace)::FailureSignalHandler()
@ 0x7fbeef1c7630 (unknown)
@ 0x7fbeee4fc387 __GI_raise
@ 0x7fbeee4fda78 __GI_abort
@ 0x15f9ba9 _ZN9__gnu_cxx27__verbose_terminate_handlerEv.cold
@ 0x4fa7726 __cxxabiv1::__terminate()
@ 0x4fa7791 std::terminate()
@ 0x4fa78e4 __cxa_throw
@ 0x1371c1d std::__throw_bad_variant_access()
@ 0x1769c50 starrocks::vectorized::BinaryColumn::append_datum()
@ 0x20fdee7 starrocks::vectorized::NullableColumn::append_datum()
@ 0x1457c1d _ZN9starrocks10vectorized10JsonReader28_construct_row_in_slot_orderEPN8simdjson8fallback8ondemand6objectEPNS0_5ChunkE.cold
@ 0x23c9575 starrocks::vectorized::JsonReader::_construct_row()
@ 0x23cd219 starrocks::vectorized::JsonReader::_read_rows<>()
@ 0x23c9891 starrocks::vectorized::JsonReader::read_chunk()
@ 0x23c9bef starrocks::vectorized::JsonScanner::get_next()
@ 0x23b79f7 starrocks::vectorized::FileScanNode::_scanner_scan()
@ 0x23b8b9f starrocks::vectorized::FileScanNode::_scanner_worker()
@ 0x5021870 execute_native_thread_routine
@ 0x7fbeef1bfea5 start_thread
@ 0x7fbeee5c496d __clone
@ 0x0 (unknown)
start time: Wed Jun 8 17:08:22 CST 2022
start time: Wed Jun 8 17:11:31 CST 2022
*** Check failure stack trace: ***
@ 0x353a97d google::LogMessage::Fail()
@ 0x353cc59 google::LogMessage::SendToLog()
@ 0x353a4f9 google::LogMessage::Flush()
@ 0x353d259 google::LogMessageFatal::~LogMessageFatal()
@ 0x15ff357 main
@ 0x7f1c2b029555 __libc_start_main
@ 0x16eb6fe (unknown)
@ (nil) (unknown)
start time: Wed Jun 8 17:15:29 CST 2022
start time: Wed Jun 8 18:03:06 CST 2022
*** Check failure stack trace: ***
@ 0x353a97d google::LogMessage::Fail()
@ 0x353cc59 google::LogMessage::SendToLog()
@ 0x353a4f9 google::LogMessage::Flush()
@ 0x353d259 google::LogMessageFatal::~LogMessageFatal()
@ 0x15ff357 main
@ 0x7f8e7df77555 __libc_start_main
@ 0x16eb6fe (unknown)
@ (nil) (unknown)
有用到外表之类的查询么。这里宕机看着和一些特定的查询有关，能查下宕机期间的fe.aduit么，可能跟那段时间的json类数据的查询有关，是什么版本？
是有用到mysql外表，starrocks是2.1.6 d44c230
找下那段时间返回时err的sql,有json类型的sql,执行sql应该是能复现问题的，然后贴下sql看看
with p1 as(
select
d.dict_key prov_code,
d.dict_name prov_name,
d.segment show_code,
r.dict_name show_name
from portal.sag_dict_detail d
left join portal.sag_dict_detail r on r.dict_type_code = ‘CUSTOM_REGION’ and d.segment = r.dict_key
where d.dict_type_code = ‘CUSTOM_ORGAN’ and d.status = 1
)
select
‘10’ as type,
t1.business_date, – 日期
t1.prov_code, – 省公司编码
t1.prov_name, – 省公司名称
t1.station_code, – 油站编码
t1.station_name, – 油站名称
t3.station_total_members, – 会员总数
t2.station_total_gasoline_members, – 汽油会员总数
t2.station_total_diesel_members, – 柴油会员总数
t3.station_total_members, – 油站会员汇总
t2.station_total_gasoline_members, – 油站汽油会员
t2.station_total_diesel_members, – 油站柴油会员
t3.station_total_members, – 站均会员数
t2.station_total_gasoline_members, – 汽油站均会员数
t2.station_total_diesel_members, – 柴油站均会员数
t1.consumed_members, – 会员消费人数
t1.gasoline_consumed_members, – 汽油会员消费人数
t1.diesel_consumed_members, – 柴油会员消费人数
t1.member_consumed_nums, – 会员消费次数
t1.member_gasoline_consumed_nums, – 会员消费次数-汽油
t1.member_diesel_consumed_nums, – 会员消费次数-柴油
t1.consumed_members/t3.station_total_members, – 会员活跃度(筛选时间范围内本公司自营站会员消费人数/截至到筛选时间点本公司会员总数)
t1.consumed_members/t3.station_total_members, – 会员活跃度-消费(筛选时间范围内自营油站会员消费人数/自营站总消费会员数)
t1.gasoline_consumed_members/t2.station_total_gasoline_members, – 汽油会员活跃度(查询时间范围内消费过的汽油会员/汽油会员总数)
t1.diesel_consumed_members/t2.station_total_diesel_members, – 柴油会员活跃度(查询时间范围内消费过的柴油会员/柴油会员总数)
COALESCE(t3.station_new_members,0) + COALESCE(t4.unconsumed_member_num, 0), – 新增会员数
t2.new_gasoline_members, – 新增汽油会员数
t2.new_diesel_members, – 新增柴油会员数
t3.station_new_members, – 有消费新增会员数
t3.station_new_members + COALESCE(t4.unconsumed_member_num, 0), – 日均新增会员数
t3.station_new_members/(COALESCE(t3.station_new_members,0)+COALESCE(t4.unconsumed_member_num, 0)), – 有消费新增会员占比(有消费的新增会员数/新增会员数)
t1.member_consumed_nums/t1.consumed_members, – 会员消费频次
t1.member_gasoline_consumed_nums/t1.gasoline_consumed_members, – 汽油会员消费频次
t1.member_diesel_consumed_nums/t1.diesel_consumed_members, – 柴油会员消费频次(会员消费次数-柴油/柴油会员消费人数)
(COALESCE(t3.station_new_members,0) + COALESCE(t4.unconsumed_member_num, 0))/t3.station_total_members, – 拉新率
– tt2.new_qiyou_members/tt2.station_total_qiyou_members, --汽油拉新率
coalesce(t2.new_gasoline_members/t1.gasoline_consumed_members, 0), – 汽油拉新率
– tt2.new_chaiyou_members/tt2.station_total_chaiyou_members, --柴油拉新率
coalesce(t2.new_diesel_members/t1.diesel_consumed_members, 0), – 柴油拉新率
t1.member_quantity_ton, – 会员总油品销量
t1.member_gasoline_quantity_ton, – 会员总汽油销量
t1.member_diesel_quantity_ton, – 会员总柴油销量
t1.member_quantity, – 会员总油品销量(L)
t1.member_gasoline_quantity, – 会员汽油油品销量(L)
t1.member_diesel_quantity, – 会员柴油油品销量(L)
t1.total_quantity_ton, – 油品总销量
t1.gasoline_quantity_ton, – 汽油总销量
t1.diesel_quantity_ton, – 柴油总销量
t1.total_quantity, – 油品总销量(L)
t1.gasoline_quantity, – 汽油总销量(L)
t1.diesel_quantity, – 柴油总销量(L)
t1.member_quantity/t1.total_quantity, – 会员消费占比-总油品
t1.member_gasoline_quantity/t1.gasoline_quantity, – 会员消费占比-汽油
t1.member_diesel_quantity/t1.diesel_quantity, – 会员消费占比-柴油
t1.member_quantity/t1.member_consumed_nums, – 会员总油品客单量
t1.member_gasoline_quantity/t1.member_gasoline_consumed_nums, – 会员汽油客单量
t1.member_diesel_quantity/t1.member_diesel_consumed_nums, – 会员柴油客单量
t1.member_real_amount/t1.member_consumed_nums, – 会员总油品客单价
t1.member_gasoline_real_amount/t1.member_gasoline_consumed_nums, – 会员汽油客单价
t1.member_diesel_real_amount/t1.member_diesel_consumed_nums, – 会员柴油客单价
acc_station_consum_count,
case when t1.consumed_members>=40 then 1 else 0 end ,
now()
from
(
select
case when p1.prov_code is not null then p1.show_code else t2.prov_code end prov_code,
case when p1.prov_code is not null then p1.show_name else t2.prov_name end prov_name,
t2.station_code,
t2.station_name,
t1.business_date,
count(distinct case when member_id is not null then member_id else null end) consumed_members, – 会员消费人数
count(distinct case when member_id is not null and oil_type = ‘Gasoline’ then member_id else NULL end) gasoline_consumed_members, – 汽油消费会员
count(distinct case when member_id is not null and oil_type = ‘Diesel’ then member_id else NULL end) diesel_consumed_members, – 柴油消费会员
count(distinct case when member_id is not null then order_id else NULL end) member_consumed_nums, – 会员消费次数
count(distinct case when member_id is not null and oil_type = ‘Gasoline’ then order_id else NULL end) member_gasoline_consumed_nums, – 会员消费次数-汽油
count(distinct case when member_id is not null and oil_type = ‘Diesel’ then order_id else NULL end) member_diesel_consumed_nums, – 会员消费次数-柴油
sum(case when member_id is not null then
case when oil_type = ‘Gasoline’ then coalesce(quantity/1350.00, 0)
when oil_type = ‘Diesel’ then coalesce(quantity/1180.00, 0)
when oil_type = ‘Gas’ then coalesce(quantity/1183.43, 0) else 0 end else 0 end) member_quantity_ton, – 油品销量
sum(case when member_id is not null and oil_type = ‘Gasoline’ then coalesce(quantity/1350.0, 0) else 0 end) member_gasoline_quantity_ton, – 汽油销量
sum(case when member_id is not null and oil_type = ‘Diesel’ then coalesce(quantity/1180.0, 0) else 0 end) member_diesel_quantity_ton, – 柴油销量
sum(case when oil_type = ‘Gasoline’ then coalesce(quantity/1350.0, 0)
when oil_type = ‘Diesel’ then coalesce(quantity/1180.0, 0)
when oil_type = ‘Gas’ then coalesce(quantity/1183.43,0) else 0 end) total_quantity_ton, – 油品总销量
sum(case when oil_type = ‘Gasoline’ then coalesce(quantity/1350.0, 0) else 0 end) gasoline_quantity_ton, – 汽油总销量
sum(case when oil_type = ‘Diesel’ then coalesce(quantity/1180.0, 0) else 0 end) diesel_quantity_ton, – 柴油销量
sum(case when member_id is not null then quantity else 0 end) member_quantity, – 会员油品销量
sum(case when member_id is not null and oil_type = ‘Gasoline’ then coalesce(quantity, 0) else 0 end) member_gasoline_quantity, – 会员汽油销量
sum(case when member_id is not null and oil_type = ‘Diesel’ then coalesce(quantity, 0) else 0 end) member_diesel_quantity, – 会员柴油销量
sum(quantity) total_quantity, – 油品总销量
sum(case when oil_type = ‘Gasoline’ then coalesce(quantity, 0) else 0 end) gasoline_quantity, – 汽油总销量
sum(case when oil_type = ‘Diesel’ then coalesce(quantity, 0) else 0 end) diesel_quantity, – 柴油销量
sum(case when member_id is not null then sale_amount - discount_amount else 0 end) member_real_amount, – 会员油品消费金额
sum(case when member_id is not null and oil_type = ‘Gasoline’ then sale_amount - discount_amount else 0 end) member_gasoline_real_amount, – 会员汽油消费金额
sum(case when member_id is not null and oil_type = ‘Diesel’ then sale_amount - discount_amount else 0 end) member_diesel_real_amount, – 会员柴油消费金额
count(distinct user_id) as acc_station_consum_count – 油站当前日期累计会员
from
falcon.fact_order_detail t1
join falcon.dim_station t2 on t1.station_code = t2.station_code
left join p1 on p1.prov_code = t2.prov_code
where t2.station_type = 2 and goods_type = ‘Oil’
and order_type = 0 and order_status = 7
group by
case when p1.prov_code is not null then p1.show_code else t2.prov_code end,
case when p1.prov_code is not null then p1.show_name else t2.prov_name end,
t2.station_code, t2.station_name, t1.business_date
) t1
left join
(
select
prov_code,
prov_name,
station_code,
station_name,
first_consume_date,
new_gasoline_members,
new_diesel_members,
sum(new_gasoline_members) over(partition by station_code order by first_consume_date rows between UNBOUNDED PRECEDING AND CURRENT ROW) station_total_gasoline_members, -- 累计汽油会员数量
sum(new_diesel_members) over(partition by station_code order by first_consume_date rows between UNBOUNDED PRECEDING AND CURRENT ROW) station_total_diesel_members -- 累计柴油会员数量
from
(
select
t2.prov_code,
t2.prov_name,
t2.station_code,
t2.station_name,
t3.cal_date first_consume_date,
count(distinct case when goods_type = 'Gasoline' then user_id else NULL end) new_gasoline_members, -- 新增汽油会员数
count(distinct case when goods_type = 'Diesel' then user_id else NULL end) new_diesel_members -- 新增柴油会员数
from falcon.dim_station t2,
portal.sag_calendar t3
left join (select * from falcon.portrait_station_member where goods_type in('Gasoline', 'Diesel')) t1 on t1.station_code = t2.station_code and t1.first_consume_date = t3.cal_date
where t3.CAL_DATE <= current_date()
group by t2.prov_code, t2.prov_name, t2.station_code
, t2.station_name, t3.cal_date
) tt2
) t2 on t1.station_code = t2.station_code and t1.business_date = t2.first_consume_date
left join
(
select
prov_code,
prov_name,
station_code,
station_name,
first_consume_date,
station_new_members,
sum(station_new_members) over(partition by station_code order by first_consume_date asc rows between UNBOUNDED PRECEDING AND CURRENT ROW) station_total_members
from
(
select
t1.prov_code,
t1.prov_name,
t1.station_code,
t1.station_name,
t2.cal_date first_consume_date,
count(*) station_new_members
from
falcon.dim_station t1,
portal.sag_calendar t2
left join (
select
t2.prov_code,
t2.prov_name,
t2.station_code,
t2.station_name,
user_id,
min(first_consume_date) first_consume_date
from falcon.portrait_station_member t1
join falcon.dim_station t2 on t1.station_code = t2.station_code
where t1.goods_type in('Gasoline', 'Diesel', 'Gas')
group by t2.prov_code, t2.prov_name, t2.station_code, t2.station_name, user_id
) ttt3 on t1.station_code = ttt3.station_code and t2.cal_date = ttt3.first_consume_date
group by t1.prov_code, t1.prov_name, t1.station_code, t1.station_name, t2.CAL_DATE
) tt3
) t3 on t1.station_code = t3.station_code and t1.business_date = t3.first_consume_date
left join
(
select
t1.station_code,
date(t1.create_time) register_date,
count(*) unconsumed_member_num
from falcon.dim_member t1
left join falcon.portrait_station_member t2 on t1.member_id = t2.user_id and t1.station_code = t2.station_code and t2.goods_type in('Gasoline', 'Diesel', 'Gas')
where t2.user_id is null
group by t1.station_code, date(t1.create_time)
) t4 on t1.station_code = t4.station_code and t1.business_date = t4.register_date
我看的是这段sql，没有json，就是有外表
你运行这个sql，be能复现宕机吗，不能的话就不是这个sql