优雅地计算各公司各类型每天的年累计值

SELECT
  now(),
  t1.sum,
  t1.crew_set_type_code,
  t2.crew_set_status_full_name,
  t1.orgz_code,
  t3.orgz_full_name,
  t3.orgz_abb_name,
  t1.period_id
FROM
  (
    with org_crew as (
      select
        distinct orgz_code,
        crew_set_type_code
      from
        mscm_gg_prd.DTF_ZN_PRD_LJSW
    ),
    everyday_value as (
      select
        t_period.period_id,
        org_crew.orgz_code,
        org_crew.crew_set_type_code,
        coalesce(t_ljsw.measure_value, 0) as value
      from
        (
          select
            distinct period_id
          from
            mscm_gg_prd.DTF_ZN_PRD_LJSW
        ) as t_period
        left join org_crew on 1 = 1
        left join mscm_gg_prd.DTF_ZN_PRD_LJSW as t_ljsw on t_ljsw.period_id = t_period.period_id
        and t_ljsw.orgz_code = org_crew.orgz_code
        and t_ljsw.crew_set_type_code = org_crew.crew_set_type_code
    )
    select
      *,
      sum(value) over(
        partition by orgz_code,
        crew_set_type_code,
        to_char(period_id, 'yyyy')
        order by
          period_id
      )
    from
      everyday_value
  ) t1
  LEFT JOIN mscm_gg_prd.ITC_GG_PRD_CREWTYP t2 on t2.crew_set_type_code = t1.crew_set_type_code
  LEFT JOIN mscm_gg_prd.ITC_GG_PRD_ORG t3 on t3.orgz_code = t1.orgz_code;