sql 명령어 예제

특정 필드값 변경하기

UPDATE financialinfo
SET year = 2023
WHERE year = 2003;

 

새로운 테이블로 옮기기

INSERT INTO financialinfo2
SELECT 
    id,
    year,
    fiscalgubun,
    industry,
    company_name,
    CASE 
        WHEN length(stock_code) < 6 THEN substr('000000' || stock_code, -6)
        ELSE stock_code
    END AS stock_code, 
    debt_ratio,
    total_asset_growth_rate,
    sales_growth_rate,
    net_profit_growth_rate,
    operating_profit_growth_rate,
    ROE,
    equity_ratio
FROM 
    financialinfo;

 

 

특정 필드 기준으로 그룹화하고, 각 그룹별로 필드의 평균을 계산한 후, 이를 테이블에 삽입

INSERT INTO industry (year, fiscalgubun, industry, debt_ratio, total_asset_growth_rate, sales_growth_rate, net_profit_growth_rate, operating_profit_growth_rate, ROE, equity_ratio, cnt)
SELECT 
    year,
    fiscalgubun,
    industry,
    AVG(debt_ratio) AS avg_debt_ratio,
    AVG(total_asset_growth_rate) AS avg_total_asset_growth_rate,
    AVG(sales_growth_rate) AS avg_sales_growth_rate,
    AVG(net_profit_growth_rate) AS avg_net_profit_growth_rate,
    AVG(operating_profit_growth_rate) AS avg_operating_profit_growth_rate,
    AVG(ROE) AS avg_ROE,
    AVG(equity_ratio) AS avg_equity_ratio,
    COUNT(*) AS cnt
FROM 
    financialinfo
WHERE
    year = 2023 AND fiscalgubun = '결산'
GROUP BY 
    year, fiscalgubun, industry;