데이터를 직접 꺼내 쓰는 법

SELECT org, vendor, contract_name, contract_amount, contract_date, bid_method
FROM contracts
WHERE region = '대전광역시'
  AND bid_method = '수의계약'
  AND contract_amount >= 100000000
ORDER BY contract_amount DESC
LIMIT 50;

SELECT org, contract_name, contract_amount, contract_date, bid_method
FROM contracts
WHERE vendor LIKE '%업체명%'     -- '업체명' 자리에 찾고 싶은 이름을 넣으세요
ORDER BY contract_date DESC;

SELECT org,
       COUNT(*) AS 계약건수,
       SUM(contract_amount) AS 총계약액
FROM contracts
WHERE region = '대전광역시'
  AND bid_method = '수의계약'
  AND contract_date >= '2023-01-01'
GROUP BY org
ORDER BY 총계약액 DESC
LIMIT 20;

SELECT org, vendor, contract_name, contract_amount, contract_date, bid_method
FROM contracts
WHERE org LIKE '%대전광역시교육청%'
  AND contract_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY contract_amount DESC;

SELECT bill_name, proposer, propose_date, committee, result
FROM bills
WHERE org LIKE '%대전광역시의회%'
ORDER BY propose_date DESC
LIMIT 50;

SELECT bill_name, org, propose_date, committee, result
FROM bills
WHERE proposer LIKE '%홍길동%'   -- 의원 이름을 넣으세요
ORDER BY propose_date DESC;

SELECT bill_name, org, proposer, propose_date, result
FROM bills
WHERE bill_name LIKE '%청년%'    -- 검색할 키워드를 넣으세요
   OR bill_name LIKE '%복지%'
ORDER BY propose_date DESC
LIMIT 100;

SELECT fiscal_year, category_name, amount
FROM settlement
WHERE gov_code = '3000000'       -- 대전광역시
  AND fiscal_year >= 2020
ORDER BY fiscal_year DESC, amount DESC
LIMIT 50;

SELECT dept_norm AS 부서명,
       dept_category AS 분류,
       COUNT(*) AS 계약건수,
       SUM(amount) AS 총액
FROM contract
WHERE gov_code = '3000000'
  AND fiscal_year >= 2022
GROUP BY dept_norm
ORDER BY 총액 DESC
LIMIT 20;

SELECT gov_code, category_name, amount
FROM settlement
WHERE gov_code IN ('3011000','3012000','3013000','3014000','3015000')
  AND fiscal_year = 2023
ORDER BY gov_code, amount DESC;

SELECT fiscal_year, category_name,
       SUM(amount) AS 총보조금액
FROM subsidy
WHERE gov_code = '3000000'
GROUP BY fiscal_year, category_name
ORDER BY fiscal_year DESC, 총보조금액 DESC
LIMIT 30;

WITH notice_src AS (
  SELECT *,
         rtrim(replace(replace(replace(replace(trim(end_date), char(160), ''), ' ', ''), '.', '/'), '-', '/'), '/') AS ds
  FROM notices
  WHERE end_date IS NOT NULL AND end_date != ''
), notice_parts AS (
  SELECT *,
         substr(ds, 1, instr(ds, '/') - 1) AS y,
         substr(ds, instr(ds, '/') + 1) AS rest1
  FROM notice_src
  WHERE instr(ds, '/') > 0
), notice_norm AS (
  SELECT title, ministry, field, start_date, end_date, url,
         date(printf(
           '%04d-%02d-%02d',
           CAST(y AS INTEGER),
           CAST(substr(rest1, 1, instr(rest1, '/') - 1) AS INTEGER),
           CAST(substr(rest1, instr(rest1, '/') + 1) AS INTEGER)
         )) AS end_date_norm
  FROM notice_parts
  WHERE instr(rest1, '/') > 0
)
SELECT title, ministry, field, start_date, end_date, url
FROM notice_norm
WHERE end_date_norm >= date('now')
ORDER BY end_date_norm ASC;

SELECT title, ministry, field, start_date, end_date, url
FROM notices
WHERE field LIKE '%환경%'        -- 분야 키워드
   OR title LIKE '%환경%'        -- 제목에 키워드 포함
ORDER BY start_date DESC
LIMIT 50;

WITH notice_src AS (
  SELECT ministry,
         rtrim(replace(replace(replace(replace(trim(start_date), char(160), ''), ' ', ''), '.', '/'), '-', '/'), '/') AS ds
  FROM notices
  WHERE start_date IS NOT NULL AND start_date != ''
), notice_parts AS (
  SELECT ministry,
         substr(ds, 1, instr(ds, '/') - 1) AS y,
         substr(ds, instr(ds, '/') + 1) AS rest1
  FROM notice_src
  WHERE instr(ds, '/') > 0
)
SELECT ministry, COUNT(*) AS 건수
FROM (
  SELECT ministry,
         date(printf(
           '%04d-%02d-%02d',
           CAST(y AS INTEGER),
           CAST(substr(rest1, 1, instr(rest1, '/') - 1) AS INTEGER),
           CAST(substr(rest1, instr(rest1, '/') + 1) AS INTEGER)
         )) AS start_date_norm
  FROM notice_parts
  WHERE instr(rest1, '/') > 0
)
WHERE start_date_norm >= date('now', '-30 days')
GROUP BY ministry
ORDER BY 건수 DESC;

SELECT org, vendor, contract_name,
       ROUND(contract_amount / 100000000.0, 1) AS 금액_억원,
       contract_date
FROM contracts
WHERE region = '대전광역시'
  AND contract_amount >= 100000000
ORDER BY contract_amount DESC
LIMIT 30;

SELECT substr(contract_date, 1, 4) AS 연도,
       COUNT(*) AS 계약건수,
       ROUND(SUM(contract_amount) / 100000000.0) AS 총액_억원
FROM contracts
WHERE region = '대전광역시'
  AND bid_method = '수의계약'
GROUP BY 연도
ORDER BY 연도 DESC;

SELECT org, vendor, contract_name, contract_amount, region
FROM contracts
WHERE (region = '대전광역시' OR region = '세종특별자치시')
  AND bid_method = '수의계약'
  AND contract_amount >= 50000000
ORDER BY contract_amount DESC
LIMIT 50;