https://linen.dev logo
Join Slack
Powered by
# troubleshooting
  • d
    select LOOKUP(fe.product_id,'dim_prod_manf_desc') as manf_desc,sum(fe.dist_total_revenue) as manf_dist_total_revenue, sales_year,sales_month,sales_quarter from fact_extrapolated_revenue fe where __time >= TIME_PARSE ('2019-01-31T000000.000Z') AND __time < TIME_PARSE ('2022-11-30T000000.000Z') AND fe.product_id IN (select dp.product_id from denorm_dim_product_unspsc dp where dp.taxonomy_id IN ('41151519','41151519','42311502','42311506','42311510','42311513','42311515','42311523','42311527','42311540','42311545','42311546','42311547','42311552','42312005','42312103','42312105','42312313','42312401')) GROUP BY LOOKUP(fe.product_id,'dim_prod_manf_desc'),sales_year,sales_month,sales_quarter order by sales_year,sales_month,sales_quarter
  • d
    But not sure how can i get a percentage calculation done on same above query
  • d
    percentage = (total revenue per year,month,quarter for a manufacturer) / ( total revenue of all manufacturer per year,month,quarter )
  • v
    use a self join
    Copy code
    select a.manf_desc,b.sales_year,b.sales_month,b.sales_quarter,(b.manf_dist_total_revenue/a.manf_total_revenue)*100 as rev_percent
(select '1' as key,LOOKUP(fe.product_id,'dim_prod_manf_desc') as manf_desc,sum(fe.dist_total_revenue) as manf_total_revenue
group by 1) a
(inner join
select '1' as key,LOOKUP(fe.product_id,'dim_prod_manf_desc') as manf_desc,sum(fe.dist_total_revenue) as manf_dist_total_revenue,
sales_year,sales_month,sales_quarter
from fact_extrapolated_revenue fe where __time >= TIME_PARSE ('2019-01-31T00:00:00.000Z') 
AND __time < TIME_PARSE ('2022-11-30T00:00:00.000Z') AND
fe.product_id IN (select dp.product_id from denorm_dim_product_unspsc dp where 
dp.taxonomy_id IN ('41151519','41151519','42311502','42311506','42311510','42311513','42311515','42311523','42311527','42311540','42311545','42311546','42311547','42311552','42312005','42312103','42312105','42312313','42312401'))
GROUP BY LOOKUP(fe.product_id,'dim_prod_manf_desc'),sales_year,sales_month,sales_quarter
order by sales_year,sales_month,sales_quarter) b on a.key=b.key
  • v
    Copy code
    select a.manf_desc,b.sales_year,b.sales_month,b.sales_quarter,(b.manf_dist_total_revenue/a.manf_total_revenue)*100 as rev_percent
from (select '1' as key,LOOKUP(fe.product_id,'dim_prod_manf_desc') as manf_desc,sum(fe.dist_total_revenue) as manf_total_revenue
group by 1) a
(inner join
select '1' as key,LOOKUP(fe.product_id,'dim_prod_manf_desc') as manf_desc,sum(fe.dist_total_revenue) as manf_dist_total_revenue,
sales_year,sales_month,sales_quarter
from fact_extrapolated_revenue fe where __time >= TIME_PARSE ('2019-01-31T00:00:00.000Z') 
AND __time < TIME_PARSE ('2022-11-30T00:00:00.000Z') AND
fe.product_id IN (select dp.product_id from denorm_dim_product_unspsc dp where 
dp.taxonomy_id IN ('41151519','41151519','42311502','42311506','42311510','42311513','42311515','42311523','42311527','42311540','42311545','42311546','42311547','42311552','42312005','42312103','42312105','42312313','42312401'))
GROUP BY LOOKUP(fe.product_id,'dim_prod_manf_desc'),sales_year,sales_month,sales_quarter
order by sales_year,sales_month,sales_quarter) b on a.key=b.key
    👍 1
  • d
    thank you
  • d
    Also one thing i noticed when i converted the dist_total_revenue to float during ingestion and then calculated the percentage without using the CAST is that float data type is taking more time to execute the query.
  • m
    hello
  • m
    i have problem with druid.dynamic.config.provider
  • m
    i want to add topic to replication but with keystore passwords hidden
  • s
    This message was deleted.
    k
    • 2
    • 1
  • m
    if I give passwords instead of variables it works
  • s
    This message was deleted.
    t
    s
    +3
    • 6
    • 53
  • s
    This message was deleted.
    s
    i
    • 3
    • 6
  • s
    This message was deleted.
    b
    s
    +2
    • 5
    • 13
  • s
    This message was deleted.
    👀 3
    s
    c
    +2
    • 5
    • 17
  • s
    This message was deleted.
    i
    d
    • 3
    • 6
  • s
    This message was deleted.
    d
    s
    s
    • 4
    • 22
  • s
    This message was deleted.
    s
    y
    c
    • 4
    • 15
  • s
    This message was deleted.
    s
    s
    • 3
    • 31
  • s
    This message was deleted.
    r
    • 2
    • 1
  • s
    This message was deleted.
    s
    • 2
    • 1
  • j
    I am using this parameter but want to mount a persistent volume to use ssd disk
  • s
    This message was deleted.
    s
    j
    • 3
    • 3
  • d
    Hi, I am using mysql RDS for druid to update the meta data . But after the ingestion is successful, i dont see the mysql DB updated. Also i dont see any table created in the DB.
  • d
    Please find below the mysql config used in druid
  • s
    This message was deleted.
    p
    d
    +4
    • 7
    • 55
1...171819...53Latest