【详述】StarRocks planner use long time 3000 ms in memo phase, This probably because 1. FE Full GC, 2. Hive external table fetch metadata took a long time, 3. The SQL is very complex. You could 1. adjust FE JVM config, 2. try query again, 3. enlarge new_planner_optimize_timeout session variable
【背景】做过哪些操作？ 复杂查询: WITH tmp1 AS (
SELECT
brand_no
FROM
brand
WHERE
brand_no IN (
'BL01',
'BL02',
'BL03',
'BS01',
'BT01',
'BT02',
'BT03',
'BT04',
'CH01',
'CL01',
'CL02',
'CL03',
'CL04',
'CM01',
'FM01',
'FM02',
'FM03',
'FM04',
'HP01',
'HP02',
'HP03',
'HP04',
'JP01',
'ML01',
'ML02',
'ML03',
'ML04',
'QS01',
'SD01',
'SD02',
'SK01',
'SK02',
'SK03',
'ST01',
'TF01',
'TF02',
'TM01',
'TT01',
'XM01'
)
),
tmp2 AS (
SELECT
order_unit_no
FROM
order_unit
WHERE
order_unit_no IN (
'Z002',
'E022',
'E150',
'C110',
'C118',
'G032',
'C130',
'G086',
'D030',
'M016',
'M002',
'E263',
'H079',
'Z218',
'Z133',
'C040',
'I032',
'I077',
'E174',
'E080',
'Z233',
'3050E',
'Z293',
'K036',
'D058',
'D086',
'D149',
'K144',
'K064',
'K196',
'K116',
'D163',
'E160',
'Z296',
'I177',
'H022',
'H056',
'D044',
'D072',
'D101',
'F070',
'F002',
'F084',
'F016',
'F098',
'C016',
'C093',
'C002',
'C026',
'C055',
'C145',
'K416',
'L012',
'K182',
'K082',
'K002',
'K158',
'F125',
'F204',
'F060',
'F120',
'F126',
'I235',
'C111',
'C120',
'Q001',
'C132',
'G095',
'D039',
'M025',
'M011',
'E262',
'H086',
'Z011',
'Z217',
'C049',
'I291',
'E089',
'3049E',
'G041',
'E183',
'D067',
'D095',
'D158',
'K045',
'K073',
'K125',
'K205',
'K153',
'D172',
'E169',
'H031',
'Z140',
'H065',
'D053',
'D081',
'D110',
'F079',
'F093',
'F011',
'F107',
'Z123',
'C011',
'C035',
'C064',
'C021',
'C096',
'C149',
'K414',
'L021',
'K191',
'K011',
'K091',
'K167',
'F122',
'F167',
'F123',
'F168',
'I411',
'I138',
'I148',
'I172',
'I186',
'I313',
'I086',
'I412',
'D025',
'I244',
'F025',
'Z012',
'C106',
'C122',
'E157',
'E029',
'G043',
'C134',
'G097',
'D041',
'M027',
'M013',
'E265',
'H088',
'Z215',
'C051',
'I088',
'I272',
'E091',
'E185',
'3048E',
'D069',
'D097',
'D160',
'K127',
'K075',
'K207',
'K047',
'K155',
'E171',
'D174',
'G136',
'H033',
'H067',
'D055',
'D083',
'D112',
'F081',
'F013',
'F109',
'F095',
'C013',
'C037',
'C066',
'C023',
'C097',
'C151',
'K415',
'L023',
'K193',
'K013',
'K093',
'K169',
'F037',
'F047',
'F057',
'F067',
'I140',
'I428',
'I174',
'I150',
'I326',
'K488',
'K490',
'K494',
'K492',
'D402',
'D404',
'D406',
'I343',
'I345',
'I347',
'I349',
'I351',
'H098',
'H103',
'H105',
'M039',
'M042',
'I427',
'I188',
'I246',
'F027',
'Z295',
'C123',
'C109',
'E158',
'E030',
'G044',
'C135',
'G098',
'D042',
'M028',
'M014',
'E264',
'H089',
'Z000',
'I127',
'Z214',
'Z134',
'C052',
'I256',
'E092',
'E186',
'Z232',
'3047E',
'D070',
'D098',
'D161',
'K156',
'K076',
'K208',
'K048',
'K128',
'E172',
'G135',
'H034',
'H068',
'D056',
'D084',
'D113',
'F082',
'F110',
'F028',
'F014',
'F096',
'C024',
'C095',
'C014',
'C038',
'C067',
'C152',
'D175',
'K417',
'L024',
'K194',
'K014',
'K094',
'K170',
'F136',
'F061',
'F121',
'F137',
'I315',
'I316',
'I141',
'I175',
'I189',
'I314',
'I089',
'I151',
'I247',
'G039',
'Z007',
'C137',
'G093',
'M023',
'M009',
'H085',
'I170',
'I184',
'I084',
'HKML',
'Z216',
'C047',
'C108',
'3055E',
'K123',
'K043',
'K203',
'K151',
'K071',
'Z200',
'YML01',
'H029',
'H063',
'D079',
'D108',
'D170',
'D051',
'D065',
'D093',
'D156',
'D037',
'F009',
'F077',
'F023',
'F091',
'F105',
'E167',
'E258',
'E087',
'E181',
'C009',
'C033',
'C062',
'C148',
'C164',
'C157',
'K419',
'L019',
'K189',
'K009',
'K089',
'K165',
'F124',
'FF01',
'I289',
'Z316',
'Z009',
'C112',
'C121',
'C133',
'G096',
'D040',
'M012',
'M026',
'E260',
'H087',
'Z220',
'C050',
'I292',
'I087',
'E090',
'E184',
'E170',
'Z231',
'3051E',
'G042',
'D068',
'D096',
'D159',
'K074',
'K046',
'K126',
'K154',
'K206',
'D173',
'YST01',
'H032',
'H066',
'D054',
'D082',
'D111',
'F080',
'F026',
'F108',
'F012',
'F094',
'ZN03',
'ZM02',
'ZM03',
'ZK29',
'ZF01',
'ZD03',
'ZD02',
'ZT07',
'ZA06',
'ZA02',
'C036',
'C065',
'C012',
'C150',
'C166',
'C159',
'I187',
'K418',
'L022',
'K012',
'K092',
'K192',
'K168',
'F049',
'F163',
'F113',
'F164',
'ZJ38',
'I245',
'Z294',
'G037',
'Z005',
'N004',
'N001',
'G091',
'D035',
'M007',
'M021',
'E257',
'H083',
'Z222',
'E085',
'E179',
'E165',
'3054E',
'Z198',
'D063',
'D091',
'D154',
'K041',
'K121',
'K149',
'D168',
'YJP01',
'H027',
'H061',
'D049',
'D077',
'D106',
'F075',
'F089',
'F103',
'F021',
'C007',
'C031',
'C060',
'K420',
'K187',
'L017',
'K007',
'K087',
'K163',
'C045',
'F043',
'F053',
'C155',
'I005',
'Z317',
'Z319',
'Z323',
'Z325',
'Z314',
'Z321',
'Z327',
'Z315',
'Z330',
'Z303',
'Z304',
'Z308',
'Z305',
'Z306',
'Z310',
'Z313',
'Z312',
'Z307',
'Z309',
'Z328',
'Z332',
'Z301',
'Z078',
'Z080',
'Z074',
'Z072',
'Z119',
'Z120',
'M106T',
'F415T',
'M109T',
'G133',
'D358',
'I9O8T',
'K9O2T',
'K9O4T',
'K9P2T',
'K9P4T',
'K9N9T',
'K9O3T',
'K9O5T',
'K9P1T',
'K9P3T',
'K9O0T',
'K9O6T',
'K9O8T',
'K9P0T',
'K9P6T',
'K9O1T',
'K9O7T',
'K9O9T',
'K9Q1T',
'T002T',
'9224T',
'H193T',
'Z128',
'T007T',
'Z129',
'D593T',
'D608T',
'D592T',
'D594T',
'X022T',
'D595T',
'D611T',
'Z079',
'X023T',
'Z135',
'Z221',
'Z223',
'Z225',
'Z226',
'G132',
'Z096',
'Z094',
'K9P5T',
'Z224',
'M112T',
'9306T',
'Z077',
'Z093',
'Z095',
'C194',
'Z073',
'K9Q2T',
'T014T',
'H050T',
'D596T',
'D369',
'D366',
'D368',
'Z298',
'Z300',
'Z299',
'D365',
'I294',
'H075',
'H044',
'D360',
'D367',
'D362',
'Z331',
'F031',
'C068',
'C190',
'F234',
'F236',
'F235',
'F237',
'K427',
'K325',
'Z092',
'L029',
'K328',
'K326',
'K329',
'K496',
'G125',
'G135T',
'G141T',
'G136T',
'G142T',
'H147T',
'Z055T',
'Z056T',
'Z057T',
'Z058T',
'Z004',
'5063T',
'Z111',
'Z113',
'Z102',
'D233',
'I902T',
'I904T',
'I906T',
'I908T',
'I910T',
'I912T',
'I903T',
'I905T',
'I907T',
'I909T',
'I911T',
'I913T',
'M033',
'M035',
'K903T',
'K905T',
'K907T',
'K9L8T',
'K9N2T',
'K904T',
'K906T',
'K908T',
'K9L9T',
'Z070T',
'D504T',
'D502T',
'D503T',
'D500T',
'C186T',
'C187T',
'H081',
'Z068',
'Z228',
'I9M0T',
'I9N1T',
'J004T',
'I9N0T',
'I9N2T',
'J008T',
'E671T',
'E673T',
'E675T',
'E677T',
'E679T',
'E681T',
'E683T',
'E685T',
'E687T',
'E689T',
'E691T',
'E693T',
'E695T',
'E697T',
'E699T',
'E701T',
'E703T',
'E705T',
'E707T',
'E709T',
'E670T',
'E672T',
'E674T',
'E676T',
'E678T',
'E680T',
'E682T',
'E684T',
'E686T',
'E688T',
'E690T',
'E692T',
'E694T',
'E696T',
'E698T',
'E700T',
'E702T',
'E704T',
'E706T',
'E708T',
'E710T',
'H149T',
'H148T',
'H150T',
'H152T'
)
),
tmp_a AS (
SELECT
inb.*
FROM
(
SELECT
MIN(bc.id) AS id,
bc.store_no,
bcd.order_unit_no,
brand.sys_no brand_unit_no,
SUM(bcd.real_qty) / COUNT(DISTINCT bc.check_date) AS check_qty
FROM
retail_pos.bill_checkstk bc
INNER JOIN retail_pos.bill_checkstk_dtl bcd ON bc.bill_no = bcd.bill_no
INNER JOIN brand ON brand.brand_no = bcd.brand_no
WHERE
1 = 1
AND bcd.brand_no IN (
'BL01',
'BL02',
'BL03',
'BS01',
'BT01',
'BT02',
'BT03',
'BT04',
'CH01',
'CL01',
'CL02',
'CL03',
'CL04',
'CM01',
'FM01',
'FM02',
'FM03',
'FM04',
'HP01',
'HP02',
'HP03',
'HP04',
'JP01',
'ML01',
'ML02',
'ML03',
'ML04',
'QS01',
'SD01',
'SD02',
'SK01',
'SK02',
'SK03',
'ST01',
'TF01',
'TF02',
'TM01',
'TT01',
'XM01'
)
AND bcd.order_unit_no IN (
'Z002',
'E022',
'E150',
'C110',
'C118',
'G032',
'C130',
'G086',
'D030',
'M016',
'M002',
'E263',
'H079',
'Z218',
'Z133',
'C040',
'I032',
'I077',
'E174',
'E080',
'Z233',
'3050E',
'Z293',
'K036',
'D058',
'D086',
'D149',
'K144',
'K064',
'K196',
'K116',
'D163',
'E160',
'Z296',
'I177',
'H022',
'H056',
'D044',
'D072',
'D101',
'F070',
'F002',
'F084',
'F016',
'F098',
'C016',
'C093',
'C002',
'C026',
'C055',
'C145',
'K416',
'L012',
'K182',
'K082',
'K002',
'K158',
'F125',
'F204',
'F060',
'F120',
'F126',
'I235',
'C111',
'C120',
'Q001',
'C132',
'G095',
'D039',
'M025',
'M011',
'E262',
'H086',
'Z011',
'Z217',
'C049',
'I291',
'E089',
'3049E',
'G041',
'E183',
'D067',
'D095',
'D158',
'K045',
'K073',
'K125',
'K205',
'K153',
'D172',
'E169',
'H031',
'Z140',
'H065',
'D053',
'D081',
'D110',
'F079',
'F093',
'F011',
'F107',
'Z123',
'C011',
'C035',
'C064',
'C021',
'C096',
'C149',
'K414',
'L021',
'K191',
'K011',
'K091',
'K167',
'F122',
'F167',
'F123',
'F168',
'I411',
'I138',
'I148',
'I172',
'I186',
'I313',
'I086',
'I412',
'D025',
'I244',
'F025',
'Z012',
'C106',
'C122',
'E157',
'E029',
'G043',
'C134',
'G097',
'D041',
'M027',
'M013',
'E265',
'H088',
'Z215',
'C051',
'I088',
'I272',
'E091',
'E185',
'3048E',
'D069',
'D097',
'D160',
'K127',
'K075',
'K207',
'K047',
'K155',
'E171',
'D174',
'G136',
'H033',
'H067',
'D055',
'D083',
'D112',
'F081',
'F013',
'F109',
'F095',
'C013',
'C037',
'C066',
'C023',
'C097',
'C151',
'K415',
'L023',
'K193',
'K013',
'K093',
'K169',
'F037',
'F047',
'F057',
'F067',
'I140',
'I428',
'I174',
'I150',
'I326',
'K488',
'K490',
'K494',
'K492',
'D402',
'D404',
'D406',
'I343',
'I345',
'I347',
'I349',
'I351',
'H098',
'H103',
'H105',
'M039',
'M042',
'I427',
'I188',
'I246',
'F027',
'Z295',
'C123',
'C109',
'E158',
'E030',
'G044',
'C135',
'G098',
'D042',
'M028',
'M014',
'E264',
'H089',
'Z000',
'I127',
'Z214',
'Z134',
'C052',
'I256',
'E092',
'E186',
'Z232',
'3047E',
'D070',
'D098',
'D161',
'K156',
'K076',
'K208',
'K048',
'K128',
'E172',
'G135',
'H034',
'H068',
'D056',
'D084',
'D113',
'F082',
'F110',
'F028',
'F014',
'F096',
'C024',
'C095',
'C014',
'C038',
'C067',
'C152',
'D175',
'K417',
'L024',
'K194',
'K014',
'K094',
'K170',
'F136',
'F061',
'F121',
'F137',
'I315',
'I316',
'I141',
'I175',
'I189',
'I314',
'I089',
'I151',
'I247',
'G039',
'Z007',
'C137',
'G093',
'M023',
'M009',
'H085',
'I170',
'I184',
'I084',
'HKML',
'Z216',
'C047',
'C108',
'3055E',
'K123',
'K043',
'K203',
'K151',
'K071',
'Z200',
'YML01',
'H029',
'H063',
'D079',
'D108',
'D170',
'D051',
'D065',
'D093',
'D156',
'D037',
'F009',
'F077',
'F023',
'F091',
'F105',
'E167',
'E258',
'E087',
'E181',
'C009',
'C033',
'C062',
'C148',
'C164',
'C157',
'K419',
'L019',
'K189',
'K009',
'K089',
'K165',
'F124',
'FF01',
'I289',
'Z316',
'Z009',
'C112',
'C121',
'C133',
'G096',
'D040',
'M012',
'M026',
'E260',
'H087',
'Z220',
'C050',
'I292',
'I087',
'E090',
'E184',
'E170',
'Z231',
'3051E',
'G042',
'D068',
'D096',
'D159',
'K074',
'K046',
'K126',
'K154',
'K206',
'D173',
'YST01',
'H032',
'H066',
'D054',
'D082',
'D111',
'F080',
'F026',
'F108',
'F012',
'F094',
'ZN03',
'ZM02',
'ZM03',
'ZK29',
'ZF01',
'ZD03',
'ZD02',
'ZT07',
'ZA06',
'ZA02',
'C036',
'C065',
'C012',
'C150',
'C166',
'C159',
'I187',
'K418',
'L022',
'K012',
'K092',
'K192',
'K168',
'F049',
'F163',
'F113',
'F164',
'ZJ38',
'I245',
'Z294',
'G037',
'Z005',
'N004',
'N001',
'G091',
'D035',
'M007',
'M021',
'E257',
'H083',
'Z222',
'E085',
'E179',
'E165',
'3054E',
'Z198',
'D063',
'D091',
'D154',
'K041',
'K121',
'K149',
'D168',
'YJP01',
'H027',
'H061',
'D049',
'D077',
'D106',
'F075',
'F089',
'F103',
'F021',
'C007',
'C031',
'C060',
'K420',
'K187',
'L017',
'K007',
'K087',
'K163',
'C045',
'F043',
'F053',
'C155',
'I005',
'Z317',
'Z319',
'Z323',
'Z325',
'Z314',
'Z321',
'Z327',
'Z315',
'Z330',
'Z303',
'Z304',
'Z308',
'Z305',
'Z306',
'Z310',
'Z313',
'Z312',
'Z307',
'Z309',
'Z328',
'Z332',
'Z301',
'Z078',
'Z080',
'Z074',
'Z072',
'Z119',
'Z120',
'M106T',
'F415T',
'M109T',
'G133',
'D358',
'I9O8T',
'K9O2T',
'K9O4T',
'K9P2T',
'K9P4T',
'K9N9T',
'K9O3T',
'K9O5T',
'K9P1T',
'K9P3T',
'K9O0T',
'K9O6T',
'K9O8T',
'K9P0T',
'K9P6T',
'K9O1T',
'K9O7T',
'K9O9T',
'K9Q1T',
'T002T',
'9224T',
'H193T',
'Z128',
'T007T',
'Z129',
'D593T',
'D608T',
'D592T',
'D594T',
'X022T',
'D595T',
'D611T',
'Z079',
'X023T',
'Z135',
'Z221',
'Z223',
'Z225',
'Z226',
'G132',
'Z096',
'Z094',
'K9P5T',
'Z224',
'M112T',
'9306T',
'Z077',
'Z093',
'Z095',
'C194',
'Z073',
'K9Q2T',
'T014T',
'H050T',
'D596T',
'D369',
'D366',
'D368',
'Z298',
'Z300',
'Z299',
'D365',
'I294',
'H075',
'H044',
'D360',
'D367',
'D362',
'Z331',
'F031',
'C068',
'C190',
'F234',
'F236',
'F235',
'F237',
'K427',
'K325',
'Z092',
'L029',
'K328',
'K326',
'K329',
'K496',
'G125',
'G135T',
'G141T',
'G136T',
'G142T',
'H147T',
'Z055T',
'Z056T',
'Z057T',
'Z058T',
'Z004',
'5063T',
'Z111',
'Z113',
'Z102',
'D233',
'I902T',
'I904T',
'I906T',
'I908T',
'I910T',
'I912T',
'I903T',
'I905T',
'I907T',
'I909T',
'I911T',
'I913T',
'M033',
'M035',
'K903T',
'K905T',
'K907T',
'K9L8T',
'K9N2T',
'K904T',
'K906T',
'K908T',
'K9L9T',
'Z070T',
'D504T',
'D502T',
'D503T',
'D500T',
'C186T',
'C187T',
'H081',
'Z068',
'Z228',
'I9M0T',
'I9N1T',
'J004T',
'I9N0T',
'I9N2T',
'J008T',
'E671T',
'E673T',
'E675T',
'E677T',
'E679T',
'E681T',
'E683T',
'E685T',
'E687T',
'E689T',
'E691T',
'E693T',
'E695T',
'E697T',
'E699T',
'E701T',
'E703T',
'E705T',
'E707T',
'E709T',
'E670T',
'E672T',
'E674T',
'E676T',
'E678T',
'E680T',
'E682T',
'E684T',
'E686T',
'E688T',
'E690T',
'E692T',
'E694T',
'E696T',
'E698T',
'E700T',
'E702T',
'E704T',
'E706T',
'E708T',
'E710T',
'H149T',
'H148T',
'H150T',
'H152T'
)
AND bc.check_type = 0
AND bc.STATUS IN (5, 100)
AND bc.sharding_flag = 'U010101_C'
AND bcd.sharding_flag = 'U010101_C'
AND length(bc.store_no) = 6
AND (bc.store_no IN('CR05ZH'))
AND bc.check_date >= str_to_date ('2022-07-13','%Y-%m-%d')
AND bc.check_date <= str_to_date ('2022-08-12', '%Y-%m-%d')
GROUP BY
bcd.order_unit_no,
bc.store_no,
brand.sys_no
) inb
INNER JOIN order_unit ou ON inb.order_unit_no = ou.order_unit_no
INNER JOIN organ o ON ou.organ_no = o.organ_no
WHERE
1 = 1
ORDER BY
inb.id
limit 0,50
),
cte_exchange AS (
SELECT
rem.shop_no,
brand.sys_no brand_unit_no,
remD.qty,
rem.business_mode
FROM
tmp_a
INNER JOIN retail_pos.return_exchange_main rem ON tmp_a.store_no = rem.shop_no
INNER JOIN retail_pos.return_exchange_dtl remD ON rem.business_no = remD.business_no
INNER JOIN brand ON brand.brand_no = remD.brand_no
AND brand.sys_no = tmp_a.brand_unit_no
INNER JOIN tmp1 ON remD.brand_no = tmp1.brand_no
WHERE
1 = 1
AND rem.out_date >= str_to_date ('2022-07-13', '%Y-%m-%d')
AND rem.out_date <= str_to_date ('2022-08-12', '%Y-%m-%d')
AND rem.STATUS >= 30
AND rem.sharding_flag = 'U010101_C'
AND remD.sharding_flag = 'U010101_C'
AND (rem.shop_no IN('CR05ZH'))
) SELECT
inbody.*, s.short_name store_name,
ou. NAME AS order_unit_name,
o. NAME organ_name,
CONCAT(z.zone_no, z. NAME) AS zone_name,
bu. NAME AS brand_en_name
FROM
(
SELECT
E.order_unit_no,
E.store_no,
E.brand_unit_no,
E.check_qty,
E.diff_qty,
E.send_out_qty,
E.out_qty,
E.exchange_qty,
SUM(
CASE
WHEN ib.bill_type = 1318
AND ib.in_out_flag = 1 THEN
ib.balance_offset
ELSE
0
END
) AS deliveryOutQty,
0 - SUM(
CASE
WHEN ib.bill_type = 1324
AND ib.in_out_flag IN (- 1, - 2, - 3) THEN
ib.balance_offset + ib.original_bad_offset
ELSE
0
END
) - SUM(
CASE
WHEN ib.bill_type = 1319
AND ib.in_out_flag = - 1 THEN
ib.balance_offset
ELSE
0
END
) AS shopToStoreInQty,
SUM(
CASE
WHEN ib.bill_type = 1317
AND ib.in_out_flag = 1 THEN
ib.balance_offset
ELSE
0
END
) AS storeTransferInQty,
0 - SUM(
CASE
WHEN ib.bill_type = 1317
AND ib.in_out_flag = - 1 THEN
ib.balance_offset
ELSE
0
END
) AS storeTransferOutQty
FROM
(
SELECT
D.order_unit_no,
D.store_no,
D.brand_unit_no,
D.check_qty,
D.diff_qty,
D.send_out_qty,
D.out_qty,
SUM(ABS(rem.qty)) exchange_qty
FROM
(
SELECT
c.order_unit_no,
c.store_no,
c.brand_unit_no,
c.check_qty,
c.diff_qty,
c.send_out_qty,
SUM(ABS(rem.qty)) out_qty
FROM
(
SELECT
b.order_unit_no,
b.store_no,
b.brand_unit_no,
b.check_qty,
b.diff_qty,
SUM(sale.qty) send_out_qty
FROM
(
SELECT
A.order_unit_no,
A.store_no,
A.brand_unit_no,
A.check_qty,
SUM(ABS(diff.diff_qty)) diff_qty
FROM
tmp_a A
LEFT JOIN (
SELECT
diff.store_no,
diffd.order_unit_no,
brand.sys_no brand_unit_no,
diffd.diff_qty
FROM
tmp_a
INNER JOIN bill_check_diff diff ON tmp_a.store_no = diff.store_no
INNER JOIN bill_check_diff_dtl diffd ON diff.bill_no = diffd.bill_no
AND tmp_a.order_unit_no = diffd.order_unit_no
INNER JOIN brand ON brand.brand_no = diffd.brand_no
AND brand.sys_no = tmp_a.brand_unit_no
INNER JOIN tmp1 ON diffd.brand_no = tmp1.brand_no
INNER JOIN tmp2 ON diffd.order_unit_no = tmp2.order_unit_no
WHERE
1 = 1
AND diff.STATUS NOT IN (0, 99)
AND diff.sharding_flag = 'U010101_C'
AND diffd.sharding_flag = 'U010101_C'
AND diff.check_type = 0
AND diff.check_date >= str_to_date ('2022-07-13', '%Y-%m-%d')
AND diff.check_date <= str_to_date ('2022-08-12', '%Y-%m-%d')
AND (diff.store_no IN('CR05ZH'))
) diff ON A.store_no = diff.store_no
AND A.order_unit_no = diff.order_unit_no
AND A.brand_unit_no = diff.brand_unit_no
GROUP BY
A.order_unit_no,
A.store_no,
A.brand_unit_no,
A.check_qty
) b
LEFT JOIN (
SELECT
sale.shop_no,
brand.sys_no brand_unit_no,
saleDtl.qty
FROM
tmp_a
INNER JOIN retail_pos.order_main sale ON tmp_a.store_no = sale.shop_no
INNER JOIN retail_pos.order_dtl saleDtl ON sale.order_no = saleDtl.order_no
INNER JOIN brand ON brand.brand_no = saleDtl.brand_no
AND brand.sys_no = tmp_a.brand_unit_no
INNER JOIN tmp1 ON saleDtl.brand_no = tmp1.brand_no
WHERE
1 = 1
AND sale.STATUS > 30
AND sale.out_date >= str_to_date ('2022-07-13', '%Y-%m-%d')
AND sale.out_date <= str_to_date ('2022-08-12', '%Y-%m-%d')
AND sale.business_type IN (0, 1)
AND sale.sharding_flag = 'U010101_C'
AND saleDtl.sharding_flag = 'U010101_C'
AND (sale.shop_no IN('CR05ZH'))
) sale ON sale.shop_no = b.store_no
AND sale.brand_unit_no = b.brand_unit_no
GROUP BY
b.order_unit_no,
b.store_no,
b.brand_unit_no,
b.check_qty,
b.diff_qty
) c
LEFT JOIN (
SELECT
*
FROM
cte_exchange
WHERE
business_mode = 2
) rem ON c.store_no = rem.shop_no
AND c.brand_unit_no = rem.brand_unit_no
GROUP BY
c.order_unit_no,
c.store_no,
c.brand_unit_no,
c.check_qty,
c.diff_qty,
c.send_out_qty
) D
LEFT JOIN (
SELECT
*
FROM
cte_exchange
WHERE
business_mode = 1
AND qty < 0
) rem ON D.store_no = rem.shop_no
AND D.brand_unit_no = rem.brand_unit_no
GROUP BY
D.order_unit_no,
D.store_no,
D.brand_unit_no,
D.check_qty,
D.diff_qty,
D.send_out_qty,
D.out_qty
) E
LEFT JOIN (
SELECT
ib.store_no,
ib.order_unit_no,
brand.sys_no brand_unit_no,
ib.bill_type,
ib.in_out_flag,
ib.balance_offset,
ib.original_bad_offset
FROM
tmp_a
INNER JOIN inventory_book ib ON tmp_a.store_no = ib.store_no
AND tmp_a.order_unit_no = ib.order_unit_no
INNER JOIN brand ON brand.brand_no = ib.brand_no
AND brand.sys_no = tmp_a.brand_unit_no
INNER JOIN tmp1 ON ib.brand_no = tmp1.brand_no
INNER JOIN tmp2 ON ib.order_unit_no = tmp2.order_unit_no
WHERE
1 = 1
AND ib.bill_date >= str_to_date ('2022-07-13', '%Y-%m-%d')
AND ib.bill_date <= str_to_date ('2022-08-12', '%Y-%m-%d')
AND ib.sharding_flag = 'U010101_C'
AND (ib.store_no IN('CR05ZH'))
) ib ON E.store_no = ib.store_no
AND E.order_unit_no = ib.order_unit_no
AND E.brand_unit_no = ib.brand_unit_no
GROUP BY
E.order_unit_no,
E.store_no,
E.brand_unit_no,
E.check_qty,
E.diff_qty,
E.send_out_qty,
E.out_qty,
E.exchange_qty
) inbody
INNER JOIN shop s ON s.shop_no = inbody.store_no
INNER JOIN order_unit ou ON inbody.order_unit_no = ou.order_unit_no
INNER JOIN organ o ON ou.organ_no = o.organ_no
INNER JOIN zone_info z ON o.zone_no = z.zone_no
INNER JOIN brand_unit bu ON bu.brand_unit_no = inbody.brand_unit_no;
【业务影响】
【StarRocks版本】例如：2.5.2
【集群规模】例如：3fe（3 follower ）+4be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如： W兆
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群9- nohup或者邮箱，谢谢
【附件】
-
fe.log/beINFO/相应截图
设置的fe JVM内存为64G 单节点内存256G -
慢查询：
- Profile信息
- 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
- pipeline是否开启：show variables like ‘%pipeline%’;
- be节点cpu和内存使用率截图
-
查询报错：根据提示都操作过了 没有起作用
