finance_data_process/doc/sql.md

sql

车辆黄皮书越界

with t101 as (
select
    TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM') as year_month,
    first_unit,
    count(1) as total,
    count(shi_fou_zhi_xing_zhong_yue_jie = '是' or null) as ls,
    count(shi_fou_zhi_xing_zhong_yue_jie = '否' or null) as wg
from
    car.car_yue_jie
where
    year_month = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM')::int4
group by
    first_unit
),
t102 as (
select
    year_month,
    car.car_second_unit_sort.second_unit,
    coalesce(total, 0) as total,
    null as bjx,
    coalesce(ls, 0) as ls,
    coalesce(wg, 0) as wg
from
    t101
right join car.car_second_unit_sort on
    t101.first_unit = car.car_second_unit_sort.second_unit
where
    car_second_unit_sort.second_unit in ('石家庄', '唐山', '秦皇岛', '邯郸', '邢台', '保定', '张家口', '承德', '廊坊', '沧州', '衡水', '雄安', '机动局', '省公司本部')
order by
    car.car_second_unit_sort.sort desc
)
select
    *
from
    t102

车辆黄皮书费用

with
t101 as (
select
    TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM') as month_id,
    second_org_no,
    second_org_name,
    round(sum(case when kpi_id in ('KPI_001', 'KPI_002', 'KPI_004', 'KPI_005', 'KPI_006', 'KPI_007', 'KPI_008', 'KPI_009', 'KPI_010', 'KPI_011', 'KPI_013', 'KPI_014', 'KPI_015', 'KPI_016', 'KPI_017', 'KPI_018', 'KPI_019', 'KPI_020', 'KPI_021', 'KPI_022', 'KPI_023') then ty_value else 0 end) / 10000, 2) as ty_fy,
    round(sum(case when kpi_id in ('KPI_001', 'KPI_002', 'KPI_004', 'KPI_005', 'KPI_006', 'KPI_007', 'KPI_008', 'KPI_009', 'KPI_010', 'KPI_011', 'KPI_013', 'KPI_014', 'KPI_015', 'KPI_016', 'KPI_017', 'KPI_018', 'KPI_019', 'KPI_020', 'KPI_021', 'KPI_022', 'KPI_023') then ly_value else 0 end) / 10000, 2) as ly_fy
from
    car.car_fee_hb
where
    second_org_no is not null
    and month_id = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYYMM')::int4
group by
    second_org_no,
    second_org_name
),
t102 as (
select
    t101.*,
    case
        when ly_fy = 0 then null
        else round((ty_fy - ly_fy) / ly_fy * 100, 2)
    end as tb
from
    t101
join car.car_second_unit_sort c on
    t101.second_org_name = c.second_unit
order by
    c.sort desc
)
select
    *
from
    t102

查询接口日志

select
    *
from
    common.nginx_log
where
    login_id != 'test'
order by
    access_time desc

查询数据处理日志

select
    *
from
    common.data_log
order by
    create_time desc