Search code examples
teradatamedian

Teradata median calculation display

The cod below gives the output as shown. I would like to display the median values for all the respective rows instead of "?". What am I doing wrong?

output

It should display the median value. 

 SELECT 
    sales_segment,
    pickup_yyyymm,
    Days,
    COUNT(*) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS no_of_records,
    SUM(Days) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS sum_days,
    AVERAGE(Days) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS AVG_days,
    Min(Days) over(partition by sales_segment,pickup_yyyymm order by sales_segment,pickup_yyyymm desc)  AS Min_days,
    MAX (Days) OVER(PARTITION BY sales_segment,pickup_yyyymm ORDER BY sales_segment,pickup_yyyymm DESC)  AS Max_days,
    CASE 
     WHEN ROW_NUMBER( ) OVER (PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days) =  COUNT(*) OVER (PARTITION BY sales_segment,pickup_yyyymm) / 2 +1
     THEN 
       CASE 
         WHEN COUNT (*) OVER (PARTITION BY  sales_segment,pickup_yyyymm) MOD 2=1 THEN Days
         ELSE AVERAGE(Days) OVER(PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days ROWS 1 PRECEDING) 
       END 
     END AS Median_Days     
    FROM
    (SELECT 
    sales_segment,
    pickup_yyyymm,
    Days
    FROM
    (SELECT 
     A.shp_pro_nbr,
    CASE 
    when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (20,21,22,23,24,25,26,27,28,29,70,71,72,73,74,75,76,77,78,79) then 'FSAD'
     when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (30,31,32,33,34,35,36,37,38,39,50,51,52,53,54,55,56,57,58,59) then 'FSMD'
     when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (40,41,42,43,44,45,46,47,48,49) then 'FSSD'
     when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 then 'FS Other'
     when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 and b.sales_area_nbr=3 then 'BSF'
     when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 then 'BSI'
     when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=8 then 'Presls'
     when b.sales_div_nbr=2 then 'WWS'
     when b.sales_div_nbr=3 then 'Specialty'
     when b.sales_div_nbr=8 then 'Non-US'
     when b.sales_div_nbr=90 then 'MKTG'
     when b.sales_div_nbr=80 then 'WWS'
     else 'None' end as sales_segment,

    --A.eff_dt,
    --A.pckup_dt,
    SUBSTR( CAST(CAST (A.pckup_dt AS DATE) AS DATE FORMAT 'yyyy/mm/dd'),1,7) AS pickup_yyyymm,
    (CAST( A.eff_dt AS DATE) - CAST (A.pckup_dt AS DATE)  ) AS Days 
     FROM ISH_FEDXFGT_PROD_VIEW_DB.fxf_ship_rev_comp  A
    INNER JOIN UI_ISH_PROD_DB.sales_quarter_end_alignment B
    ON A.payor_cust_nbr = B.cf_cust_nbr AND B.align_typ_cd ='P'AND  fscl_qtr_nbr = 4 AND fscl_yr_nbr = 2016 AND B.prim_cvge_flg= 'Y'
    AND CAST(A.pckup_dt AS DATE) BETWEEN ADD_MONTHS(CURRENT_DATE,-24) AND CURRENT_DATE 
    GROUP BY
    1,2,3,4
    ) a
    GROUP BY 1,2,3)b
    --QUALIFY ROW_NUMBER( ) OVER (PARTITION BY b. sales_segment,b.pickup_yyyymm ORDER BY b.days)= COUNT(*)  OVER PARTITION BY b.sales_segment,b. pickup_yyyymm) /2+1; 
    --GROUP BY 1,2;
    --ORDER BY 1,2
Solution

  • The last query in my post on MEDIAN shows how to get the it as OLAP result, you need to add another nesting level:

    SELECT 
   sales_segment,
   pickup_yyyymm,
   ...,
   MIN(Median_Days) over(partition by sales_segment,pickup_yyyymm) AS Median_Days
FROM
 (
   SELECT 
       sales_segment,
       pickup_yyyymm,
       Days,
       COUNT(*) over(partition by sales_segment,pickup_yyyymm)  AS no_of_records,
       SUM(Days) over(partition by sales_segment,pickup_yyyymm)  AS sum_days,
       AVERAGE(Days) over(partition by sales_segment,pickup_yyyymm)  AS AVG_days,
       Min(Days) over(partition by sales_segment,pickup_yyyymm)  AS Min_days,
       MAX (Days) OVER(PARTITION BY sales_segment,pickup_yyyymm)  AS Max_days,
       CASE 
        WHEN ROW_NUMBER( ) OVER (PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days) = COUNT(*) OVER (PARTITION BY sales_segment,pickup_yyyymm) / 2 +1
        THEN 
          CASE 
            WHEN COUNT (*) OVER (PARTITION BY sales_segment,pickup_yyyymm) MOD 2=1 THEN Days
            ELSE AVERAGE(Days) OVER(PARTITION BY sales_segment,pickup_yyyymm ORDER BY Days ROWS 1 PRECEDING) 
          END 
        END AS Median_Days     
    FROM
     (
       SELECT 
          sales_segment,
          pickup_yyyymm,
          Days
       FROM
        (
          SELECT 
             A.shp_pro_nbr,
             CASE 
               when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (20,21,22,23,24,25,26,27,28,29,70,71,72,73,74,75,76,77,78,79) then 'FSAD'
               when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (30,31,32,33,34,35,36,37,38,39,50,51,52,53,54,55,56,57,58,59) then 'FSMD'
               when b.sales_div_nbr=1 and b.sales_grp_nbr<>2  and b.sales_terr_nbr in (40,41,42,43,44,45,46,47,48,49) then 'FSSD'
               when b.sales_div_nbr=1 and b.sales_grp_nbr<>2 then 'FS Other'
               when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 and b.sales_area_nbr=3 then 'BSF'
               when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=7 then 'BSI'
               when b.sales_div_nbr=1 and b.sales_grp_nbr=2 and b.sales_org_nbr=8 then 'Presls'
               when b.sales_div_nbr=2 then 'WWS'
               when b.sales_div_nbr=3 then 'Specialty'
               when b.sales_div_nbr=8 then 'Non-US'
               when b.sales_div_nbr=90 then 'MKTG'
               when b.sales_div_nbr=80 then 'WWS'
             else 'None' end as sales_segment,

            --A.eff_dt,
            --A.pckup_dt,
            SUBSTR( CAST(CAST (A.pckup_dt AS DATE) AS DATE FORMAT 'yyyy/mm/dd'),1,7) AS pickup_yyyymm,
            (CAST( A.eff_dt AS DATE) - CAST (A.pckup_dt AS DATE)  ) AS Days 
          FROM ISH_FEDXFGT_PROD_VIEW_DB.fxf_ship_rev_comp  A
          INNER JOIN UI_ISH_PROD_DB.sales_quarter_end_alignment B
          ON A.payor_cust_nbr = B.cf_cust_nbr AND B.align_typ_cd ='P'AND  fscl_qtr_nbr = 4 AND fscl_yr_nbr = 2016 AND B.prim_cvge_flg= 'Y'
          AND CAST(A.pckup_dt AS DATE) BETWEEN ADD_MONTHS(CURRENT_DATE,-24) AND CURRENT_DATE 
          GROUP BY 1,2,3,4
        ) a
       GROUP BY 1,2,3
    )b
 ) as dt

    I removed all the order by sales_segment,pickup_yyyymm desc because it's not needed.

    Another remark on the pickup_yyyymm calculation, you don't need a substring:

    TRIM(CAST (A.pckup_dt FORMAT 'yyyy/mm')) AS pickup_yyyymm,

    Would be more efficient if there's no cast to string at all:

    EXTRACT(YEAR FROM A.pckup_dt) * 100 + EXTRACT(MONTH FROM A.pckup_dt)