https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
a
can you share the output you get if you try to explain the query?
d
hi @Abhishek Agarwal here is the aggregated result i get if i remove (OR condition)
EXPR$0 sales_year sales_month sales_quarter 1130297838 2019 1 1 1243378194 2019 10 4 1174913950 2019 11 4 1354069796 2019 12 4 1108412262 2019 2 1 1252788091 2019 3 1 1139362573 2019 4 2
with OR condition, i get above error
a
this seems like a bug.
it will be helpful if you can share some sample data schema on which you can reproduce this issue
d
Hi @Abhishek Agarwal here is the table and sample data
denorm_fact_extrapolated_unspsc
__time product_id monthenddatekey sales_year sales_month sales_quarter dist_total_revenue dist_total_units facility_type unspsc_1 unspsc_2 unspsc_3 unspsc_4 taxonomy_id manf_desc prod_desc sku direct_total_revenue direct_total_units 2017-01-31T000000.000Z 1000003 20170131 2017 1 1 73 1 PHYSICIAN OFFICE Medical Equipment and Accessories and Supplies Surgical products Surgical clamps and forceps and surgical ligators and related instruments Surgical clamps or clips or forceps 42291802 DERRON SURGICAL INSTRUMENTS FORCEP TISSUE 5" STRAIGHT JAW STAINLESS STEEL EA 42138 73 1 2017-01-31T000000.000Z 1000006 20170131 2017 1 1 340 1 PHYSICIAN OFFICE Medical Equipment and Accessories and Supplies Patient care and treatment products and supplies Vascular and compression therapy equipment and supplies Vascular or compression apparel or support 42142802 DEVON MEDICAL PUMP DVT SYSTEM CIRONA 6100 EA 6100 340 1 2017-01-31T000000.000Z 1000056 20170131 2017 1 1 1128 34 LONG-TERM CARE Medical Equipment and Accessories and Supplies Medical cleaning and sterilization products Cold sterilization and disinfectant solutions Medical surface disinfectants 42281604 DIVERSEY DISINFECTANT,EXPOSE II 256, 2X2L, JFILL 5699 1128 34 2017-01-31T000000.000Z 1000062 20170131 2017 1 1 267 12 LONG-TERM CARE Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Floor cleaners 47131801 DIVERSEY DBD-MBO-SHAMPOO,CARPET, HIGH FOAM, 4 X 1 5002689 267 12 2017-01-31T000000.000Z 1000064 20170131 2017 1 1 57 12 LONG-TERM CARE Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Furniture polish or waxes 47131806 DIVERSEY POLISH FURNITURE PLEDGE 10OZ DISC BY MFG-RPL NOT SET UP 723725 57 12 2017-01-31T000000.000Z 1000066 20170131 2017 1 1 402 192 HOSPITAL/HEALTH SYSTEM Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Toilet cleaners 47131829 DIVERSEY CLEANER,BOWL,CREW,MILD ACID,1X32OZ 94476081 402 192 2017-01-31T000000.000Z 1000067 20170131 2017 1 1 327 5 HOSPITAL/HEALTH SYSTEM Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Stain cleaners or removers 47131827 DIVERSEY STRIPPER,FLOOR,ULTRA,5GAL,BOX 95386176 327 5 2017-01-31T000000.000Z 1000086 20170131 2017 1 1 61 1 TREATMENT CENTER Medical Equipment and Accessories and Supplies Orthopedic and prosthetic and sports medicine products Orthopedic softgoods for lower extremity Knee therapeutic brace or support 42241703 DJO GLOBAL BRACE KNEE PATELLA RIGHT XXXLARGE 1102607 61 1 2017-01-31T000000.000Z 1000086 20170131 2017 1 1 154 2 PHYSICIAN OFFICE Medical Equipment and Accessories and Supplies Orthopedic and prosthetic and sports medicine products Orthopedic softgoods for lower extremity Knee therapeutic brace or support 42241703 DJO GLOBAL BRACE KNEE PATELLA RIGHT XXXLARGE 1102607 154 2 2017-01-31T000000.000Z 1000086 20170131 2017 1 1 536 9 HOSPITAL/HEALTH SYSTEM Medical Equipment and Accessories and Supplies Orthopedic and prosthetic and sports medicine products Orthopedic softgoods for lower extremity Knee therapeutic brace or support 42241703 DJO GLOBAL BRACE KNEE PATELLA RIGHT XXXLARGE 1102607 536 9
denorm_manf_entitlements
__time email_address manufacturer_desc data_frequency geography_entitlement unspsc_code cot 2023-04-18T062542.242Z 4s@dr.com 4S H Quarterly Zip 42132205 2023-04-18T062542.242Z a.a@c.com CLAR Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"] 2023-04-18T062542.242Z a.p@cl.com 3B ["14111539","31162506","31201512","41112601","41113035","41113117","41116116","41116132","42131613","42131701","42131713","42131721","42141503","42142105","42142106","42142108","42142111","42142535","42143101","42152453","42172105","42181702","42181707","42181708","42181715","42181716","42181904","42182101","42182103","42182105","42182107","42182109","42182110","42182114","42182207","42203502","42203503","42221507","42221604","42221703","42221708","42221803","42222307","42241502","42241504","42241505","42241506","42241507","42241510","42281607","42281802","42281803","42281804","42281805","42281806","42281807","42281809","42281810","42281904","42281907","42281909","42294211","42294927","42295104","42295143","42295408","42295414","42295415","42295421","42295424","42295480","42311502","42311506","42311510","42311513","42311514","42311515","42311518","42311527","42311531","42311540","42311543","42311545","42311546","42311547","42311552","42311554","42312005","42312010","42312018","42312103","42312105","42312313","42312401","42312601","42312602","42312603","44121605","44121613","46181802","46181804","46182001","46182002","46182005","46182007","47131602","47131704","49241704","52151644","53131607","53131608","53131613","53131616","53131626","60104202"] 2023-04-18T062542.242Z a2_test@do.com MED Quarterly National ["11223344","22334455","41151519"] ["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"] 2023-04-18T062542.242Z aa@cl.com CLA Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"] 2023-04-18T062542.242Z aa@cla.com CLA Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"] 2023-04-18T062542.242Z a@cl.com 3MI ["14111539","31162506","31201512","41112601","41113035","41113117","41116116","41116132","42131613","42131701","42131713","42131721","42141503","42142105","42142106","42142108","42142111","42142535","42143101","42152453","42172105","42181702","42181707","42181708","42181715","42181716","42181904","42182101","42182103","42182105","42182107","42182109","42182110","42182114","42182207","42203502","42203503","42221507","42221604","42221703","42221708","42221803","42222307","42241502","42241504","42241505","42241506","42241507","42241510","42281607","42281802","42281803","42281804","42281805","42281806","42281807","42281809","42281810","42281904","42281907","42281909","42294211","42294927","42295104","42295143","42295408","42295414","42295415","42295421","42295424","42295480","42311502","42311506","42311510","42311513","42311514","42311515","42311518","42311527","42311531","42311540","42311543","42311545","42311546","42311547","42311552","42311554","42312005","42312010","42312018","42312103","42312105","42312313","42312401","42312601","42312602","42312603","44121605","44121613","46181802","46181804","46182001","46182002","46182005","46182007","47131602","47131704","49241704","52151644","53131607","53131608","53131613","53131616","53131626","60104202"] 2023-04-18T062542.242Z aa@clcom CLAR Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"] 2023-04-18T062542.242Z aa@cl.com CLARIVA Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"] 2023-04-18T062542.242Z a@cl.com MED Quarterly National ["11223344","22334455","41151519"] ["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"]
a
d
sure i will do that
j
Doing joins on one side of an OR clause has always been problematic in my experience as it's not a very "relational" thing to do. Can you change that "or in()" clause to an outer join that you always do against the subquery and then change that where condition to
Copy code
where fe.manf_desc = 'BDD' OR fe.manf_desc = outer_table.top5manf_desc
to see if it gets around the error?
d
sure @John Kowtko
Hi @John Kowtko The below query works but performance is not that good . Not sure this is what you meant
with me_revenue as (select SUM(fe.dist_total_revenue) as me_rev,
fe.sales_year,
fe.sales_month,
fe.sales_quarter from denorm_fact_extrapolated_unspsc fe
-- INNER join
where __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')
AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
AND fe.manf_desc = 'BD'
AND fe.taxonomy_id IN(
SELECT
unspsc_code
FROM
denorm_manf_entitlements
WHERE
email_address= '<mailto:a@dr.com|a@dr.com>'
)
group by fe.sales_year,
fe.sales_month,
fe.sales_quarter
),
manf_total_revenue as (select SUM(fe.dist_total_revenue) as manf_tot_rev,
fe.sales_year,
fe.sales_month,
fe.sales_quarter from denorm_fact_extrapolated_unspsc fe
-- INNER join
where  fe.manf_desc in (SELECT
manf_desc as top5manf_desc
FROM
denorm_fact_extrapolated_unspsc fes
where
__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')
AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
AND fes.manf_desc != 'BD'
and fes.taxonomy_id in (
SELECT
unspsc_code
FROM
denorm_manf_entitlements
where
email_address= '<mailto:a@dr.com|a@dr.com>'
)
group by
manf_desc
order by
SUM(fes.dist_total_revenue) desc
limit
5
)
-- top5manf on manf_desc = top5manf_desc
-- WHERE
AND
__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')
AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
-- or fe.manf_desc = 'BD'
AND fe.taxonomy_id IN(
SELECT
unspsc_code
FROM
denorm_manf_entitlements
WHERE
email_address= '<mailto:a@dr.com|a@dr.com>'
)
group by fe.sales_year,
fe.sales_month,
fe.sales_quarter
),
temp_table as (SELECT
manf_desc as top5manf_desc
FROM
denorm_fact_extrapolated_unspsc fes
where
__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')
AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
AND fes.manf_desc != 'BD'
and fes.taxonomy_id in (
SELECT
unspsc_code
FROM
denorm_manf_entitlements
where
email_address= '<mailto:a@dr.com|a@dr.com>'
)
group by
manf_desc
order by
SUM(fes.dist_total_revenue) desc
limit
5
),
manf_revenue AS (
SELECT
fe.manf_desc AS manf_desc,
SUM(fe.dist_total_revenue) AS manf_dist_total_revenue,
sales_year,
sales_month,
sales_quarter
FROM
denorm_fact_extrapolated_unspsc fe
WHERE
__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')
AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
AND fe.taxonomy_id IN (
SELECT
unspsc_code
FROM
denorm_manf_entitlements
WHERE
email_address= '<mailto:a@dr.com|a@dr.com>'
)
AND (fe.manf_desc ='BD'
OR fe.manf_desc IN ( select top5manf_desc from temp_table))
GROUP BY
fe.manf_desc,
sales_year,
sales_month,
sales_quarter
),
total_revenue AS (
SELECT
SUM(fes.dist_total_revenue) AS total_rev,
sales_year,
sales_month,
sales_quarter
FROM
denorm_fact_extrapolated_unspsc fes
WHERE
__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')
AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
AND fes.taxonomy_id IN(
SELECT
unspsc_code
FROM
denorm_manf_entitlements
WHERE
email_address= '<mailto:a@dr.com|a@dr.com>'
)
GROUP BY
sales_year,
sales_month,
sales_quarter
)
SELECT
manf_desc,
mr.sales_year,
mr.sales_month,
mr.sales_quarter,
total_rev as total_revenue,
manf_dist_total_revenue,
(
manf_dist_total_revenue / total_rev
)* 100 AS percentage,
(total_rev - (me_rev + manf_tot_rev)) as others_revenue,
((total_rev - (me_rev + manf_tot_rev)) / total_rev)* 100 AS others_percentage
FROM
manf_revenue mr
JOIN total_revenue tr ON mr.sales_year = tr.sales_year
AND mr.sales_month = tr.sales_month
AND mr.sales_quarter = tr.sales_quarter
JOIN me_revenue ot ON mr.sales_year = ot.sales_year
AND mr.sales_month = ot.sales_month
AND mr.sales_quarter = ot.sales_quarter
JOIN manf_total_revenue mtr ON mr.sales_year = mtr.sales_year
AND mr.sales_month = mtr.sales_month
AND mr.sales_quarter = mtr.sales_quarter
Hi @Abhishek Agarwal i still created an issue here at https://github.com/apache/druid/issues/14206
j
hi DK, you still have the "OR ... IN()" phrasing in there ... try changing that CTE to something like this:
Copy code
manf_revenue AS (
 SELECT fe.manf_desc AS manf_desc, 
        SUM(fe.dist_total_revenue) AS manf_dist_total_revenue, 
        sales_year, 
        sales_month, 
        sales_quarter 
   FROM denorm_fact_extrapolated_unspsc fe
   LEFT JOIN temp_table t on fe.manf_desc = t.top5manf_desc
  WHERE __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z') 
    AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
    AND fe.taxonomy_id IN (
         SELECT unspsc_code 
           FROM denorm_manf_entitlements 
          WHERE email_address= '<mailto:a@dr.com|a@dr.com>'
        ) 
    AND (fe.manf_desc ='BD' OR fe.manf_desc = t.top5manf_desc)
  GROUP BY 
        fe.manf_desc, 
        sales_year, 
        sales_month, 
        sales_quarter
),
Open in Slack
PreviousNext
Powered by