[text] mrt

Viewer

  1. with full_time as (   --собираем список полей для 
  2.     select dy.name::numeric  as yr
  3.         ,coalesce(substring(dp.name,'^[0-9]{1}') ,'5')  as prd --dp.id::numeric - 1 
  4.         ,dp.id::numeric as prd_id
  5.         ,dk.*
  6.         ,case when upper(dk.l3_name) = upper('прочее') then dk.l3_name || ' ' || dk.l2_name else null end cfo_chngd
  7.     from dds.d_year dy 
  8.     cross join dds.d_kpi_period dp 
  9.     cross join dds.d_kpi dk 
  10. )
  11. -- ////////////// НАЧАЛО КАСТОМНОЙ ЧАСТИ \\\\\\\\\\\\\\
  12. ,facts as (
  13. select ds."year" as yr 
  14. ,ds.quarter as qrt 
  15. ,ds.deal_sum_sald as value
  16. ,kc.kpi_id as kpi_code
  17. ,ds.cfo
  18. from dds.deal_status ds 
  19. left join dds.kpi_code kc 
  20. on trim(lower(ds.deal_id)) = trim(lower(kc.deal_id))
  21. and trim(ds.cfo) = trim(kc.cfo_name)
  22. and kc.kpi_id ~ '18\.+'
  23. where ds.status = 'В портфеле'
  24. and extract(year from ds.contract_date) = extract(year from now()) 
  25.     and ds.programm like '%ФНБ%'
  26.     ),    
  27. --////////////////// КОНЕЦ КАСТОМНОЙ ЧАСТИ \\\\\\\\\\\\\\\\
  28. pre_final as (
  29. select 
  30. ft.yr,ft.prd,ft.kpi_category
  31. ,ft.l1_code,ft.l1_name
  32. ,ft.l2_code,ft.l2_name
  33. ,ft.l3_code,ft.l3_name
  34. ,ft.l4_code,ft.l4_name
  35. ,sum(ts.value/ft.unit_amt::numeric) as fact_amount
  36. ,ft.unit
  37. from full_time ft
  38. left join facts ts
  39. on 1 = case when ts.kpi_code is null and upper(ft.l3_name) = upper('прочее') and ts.cfo = ft.l2_name then 1 --прочее агрегируются по цфо
  40.                         when ts.kpi_code = coalesce(ft.l4_code,ft.l3_code,ft.l2_code,ft.l1_code) then 1 --мапим все записи
  41.                         when coalesce(ft.l4_code,ft.l3_code,ft.l2_code,ft.l1_code) = ft.l1_code then 1 --если запись 1 уровня, то собираем всё
  42.                         when ts.cfo = coalesce(ft.l3_name,ft.l2_name) then 1 --если запись 2 уровня, то собираем по ЦФО
  43.                         else 0 end
  44. and ft.yr = ts.yr::numeric 
  45. and 1 = case when ft.prd::numeric >= ts.qrt::numeric   then 1 --сумма с накоплением по кварталам
  46.                     --when ft.prd::numeric = 0 then 1 
  47.                     else 0 end  --агрегация всех записей на строчку года. в справочнике периодов id на год = 1, id кварталов - омер квартала+1
  48. where ft.l1_code = '18.'  --Иного не придумали
  49. and ft.yr = ts.yr
  50. group by ft.yr,ft.prd,ft.kpi_category
  51. ,ft.l1_code,ft.l1_name
  52. ,ft.l2_code,ft.l2_name
  53. ,ft.l3_code,ft.l3_name
  54. ,ft.l4_code,ft.l4_name
  55. ,ft.unit
  56. )
  57. select  
  58. ft.yr,dp.name as prd
  59. ,ft.l1_name as main_kpi
  60. ,ft.kpi_category as main_kpi_short
  61. ,coalesce(ft.l4_code,ft.l3_code,ft.l2_code,ft.l1_code) as kpi_code
  62. ,coalesce(ft.l4_name,ft.l3_name,ft.l2_name,ft.l1_name) as kpi_name
  63. ,ft.unit
  64. ,ft.fact_amount as value_fact
  65. ,gd.value as value_plan 
  66. from pre_final ft
  67. join dds.d_kpi_period dp
  68.         on coalesce(substring(dp.name,'^[0-9]{1}'),'5') = ft.prd
  69. join dds.d_year dy 
  70.         on dy."name"::numeric = ft.yr
  71. join dds.d_kpi gp --для джоина kpi_gd по id
  72.         on coalesce(gp.l4_code,gp.l3_code,gp.l2_code,gp.l1_code) = coalesce(ft.l4_code,ft.l3_code,ft.l2_code,ft.l1_code)
  73. left join dds.kpi_gd gd --dds.kpi_gd как нормализованная таблица с планом.
  74.         on gd.kpi_gp_id = gp.id 
  75.         and 1= case when gd.period_id - 1 = ft.prd::numeric  then 1
  76.                 when gd.period_id = 1 and ft.prd::numeric  = 5 then 1
  77.                 else 0 end
  78.         and gd.year_id = dy.id
  79.         and gd.load_id=(select max(load_id) from dds.kpi_gd )
  80. left join dds.d_kpi_datatype ddt --фильтр по плану
  81.         on gd.datatype_id = ddt.id 
  82.         and ddt.id = 1

Editor

You can edit this paste and save as new:


File Description
  • mrt
  • Paste Code
  • 28 Sep-2023
  • 3.57 Kb
You can Share it: