v_chain (view)

Data license: 비공개

85,892 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: gov_code, gov_name, fiscal_year, dept_category, contract_category, method, is_negotiated

Next page

CREATE VIEW v_chain AS
SELECT
    c.contract_id,
    c.gov_code,
    g.gov_name,
    c.fiscal_year,
    c.dept_norm,
    c.dept_category,
    c.contract_name,
    c.category         AS contract_category,
    c.method,
    c.is_negotiated,
    c.vendor,
    c.vendor_bizno,
    c.amount           AS contract_amount,
    -- settlement 교차: 같은 gov+연도의 민간이전 총액
    (SELECT SUM(s.amount)
     FROM settlement s
     WHERE s.gov_code = c.gov_code
       AND s.fiscal_year = c.fiscal_year
       AND s.category_code = '30700'
    )                  AS settlement_민간이전_총액,
    -- 괴리 플래그: settlement 데이터 없으면 1
    CASE WHEN EXISTS(
        SELECT 1 FROM settlement s
        WHERE s.gov_code = c.gov_code AND s.fiscal_year = c.fiscal_year
    ) THEN 0 ELSE 1 END AS gap_no_settlement,
    c.source_table,
    c.source_file
FROM contract c
LEFT JOIN gov g ON c.gov_code = g.gov_code;