SQL 문제 해결 - 동일 행에 값 합치기

SQL 문제 해결 - 동일 행에 값 합치기

2022. 2. 17. 23:47ㆍ개념 정리 작업실/Data Base

728x90

🚀 Code Content

🔽 Service.java

@Slf4j
@Service("junyharangSaleDailyStatisticService")
public class JunyHarangSaleDailyStatisticService implements LogicRepository {

    @Autowired
    JdbcQueryFactory query;

    @Autowired
    JunyHarangoctDao junyharangoctdao;

    @Override
    public List<Map<String, Object>> list(QueryContext context) {
        Map<String,Object> data = context.getData();
        if (data == null) {
            throw new ApiException("500", "data is null");
        }

        int page = JsonUtils.getIntValue(data, "page");
        int size = JsonUtils.getIntValue(data, "pageSize");

        String period_fromto = JsonUtils.getStringValue(data, "period_fromto");
        String channel_matching = JsonUtils.getStringValue(data, "channel_matching");

        String from ="";
        String to ="";
        String sql = "";
        int term = period_fromto.length();

        if(term == 21){

            if (!StringUtils.isEmpty(period_fromto)) {
                String[] tmp = period_fromto.split("~");
                from = tmp[0].replace("-","");
                to = tmp[1].replace("-","");
            }

            sql =   " SELECT a.* from( " +
                    " select to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM-dd') period, " +
                    " a.sales_amt sales, " +
                    " a.pg_commission_amt pg_fee, " +
                    " a.company_settlement_amt store_settlement_amt, " +
                    " a.self_pay_coupun coupon, " +
                    " a.point , " +
                    " a.self_goods_deli_amt delivery_fee, " +
                    " a.tot_settlement_amt final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND a.sales_date >= "+ from +" " +
                    " AND a.sales_date <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " UNION ALL " +
                    " select '합계' period, " +
                    " nvl(sum(a.sales_amt),0) sales, " +
                    " nvl(sum(a.pg_commission_amt),0) pg_fee, " +
                    " nvl(sum(a.company_settlement_amt),0) store_settlement_amt, " +
                    " nvl(sum(a.self_pay_coupun),0) coupon, " +
                    " nvl(sum(a.point),0) point, " +
                    " nvl(sum(a.self_goods_deli_amt),0) delivery_fee, " +
                    " nvl(sum(a.tot_settlement_amt),0) final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND a.sales_date >= "+ from +" " +
                    " AND a.sales_date <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " )a " +
                    " ORDER BY (CASE WHEN a.period = '합계' THEN 2 ELSE 1 END), a.period DESC ";
        }else {

                if (!StringUtils.isEmpty(period_fromto)) {
                    String[] tmp = period_fromto.split("~");
                    from = tmp[0].replace("-","");
                    to = tmp[1].replace("-","");
                }

            sql =   " SELECT a.* from( " +
                    " select to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM') period, " +
                    " a.sales_amt sales, " +
                    " a.pg_commission_amt pg_fee, " +
                    " a.company_settlement_amt store_settlement_amt, " +
                    " a.self_pay_coupun coupon, " +
                    " a.point, " +
                    " a.self_goods_deli_amt delivery_fee, " +
                    " a.tot_settlement_amt final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND SUBSTR(a.sales_date,-8,6) >= "+ from +" " +
                    " AND SUBSTR(a.sales_date,-8,6) <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " UNION ALL " +
                    " select '합계' period, " +
                    " nvl(sum(a.sales_amt),0) sales, " +
                    " nvl(sum(a.pg_commission_amt),0) pg_fee, " +
                    " nvl(sum(a.company_settlement_amt),0) store_settlement_amt, " +
                    " nvl(sum(a.self_pay_coupun),0) coupon, " +
                    " nvl(sum(a.point),0) point, " +
                    " nvl(sum(a.self_goods_deli_amt),0) delivery_fee, " +
                    " nvl(sum(a.tot_settlement_amt),0) final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND SUBSTR(a.sales_date,-8,6) >= "+ from +" " +
                    " AND SUBSTR(a.sales_date,-8,6) <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " )a " +
                    " ORDER BY (CASE WHEN a.period = '합계' THEN 2 ELSE 1 END), a.period DESC ";
        }

        log.info("sql - " + sql);
        List<Map<String, Object>> list = (List<Map<String, Object>>)query.list(sql, page, size, "zinSaleDailyStatistic").get("items");
        return list;
    }

    @Override
    public Map<String, Object> read(String id) {
        return null;
    }

    @Override
    public int create(Node node) {
        return 0;
    }

    @Override
    public int update(Node node) {
        return 0;
    }

    @Override
    public int delete(String id) {
        return 0;
    }

    private void replaceFilterList(Map<String,Object> params, String filterKey, String listKey) {
        List<String> filter = JsonUtils.getStringList(params, filterKey);
        params.put(listKey, filter);
    }
}

🔽 SQL문

SELECT * FROM ( 
SELECT ROWNUM AS RNUM, PL.* 
FROM ( 
SELECT * 
FROM (  
SELECT a.* 
from(  
select to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM') period,  a.sales_amt sales,  a.pg_commission_amt pg_fee,  a.company_settlement_amt store_settlement_amt,  a.self_pay_coupun coupon,  a.point,  a.self_goods_deli_amt delivery_fee,  a.tot_settlement_amt final_settlement_amt  
from tbl_stat_sales_cnt a  
WHERE 1=1  
AND SUBSTR(a.sales_date,-8,6) >= 202108  
AND SUBSTR(a.sales_date,-8,6) <= 202202  
UNION ALL  
select '합계' period,  nvl(sum(a.sales_amt),0) sales,  nvl(sum(a.pg_commission_amt),0) pg_fee,  nvl(sum(a.company_settlement_amt),0) store_settlement_amt,  nvl(sum(a.self_pay_coupun),0) coupon,  nvl(sum(a.point),0) point,  nvl(sum(a.self_goods_deli_amt),0) delivery_fee,  nvl(sum(a.tot_settlement_amt),0) final_settlement_amt  
from tbl_stat_sales_cnt a  
WHERE 1=1  
AND SUBSTR(a.sales_date,-8,6) >= 202108  AND SUBSTR(a.sales_date,-8,6) <= 202202  )a  
ORDER BY (CASE WHEN a.period = '합계' THEN 2 ELSE 1 END), a.period DESC  ) 
WHERE ROWNUM <= 10 ) PL ) 
WHERE RNUM > 0;

⚠️ 문제 발생!

위의 결과와 같이 PERIOD에 2022-02 컬럼 두 개가 합쳐져서 하나로 나와야 하는데, 두 개로 쪼개져서 나오는 것이에요.

🍻 문제 해결!

SELECT *
FROM (
select to_char(to_date(sales_date, 'yyyyMMdd'), 'yyyy-MM') period, sum(sales_amt) AS sales, sum(pg_commission_amt) AS pg_fee, sum(company_settlement_amt) AS store_settlement_amt, sum(self_pay_coupun) AS coupon, sum(point) AS point, sum(self_goods_deli_amt) AS delivery_fee, sum(tot_settlement_amt) AS final_settlement_amt
from tbl_stat_sales_cnt a  
WHERE 1=1
AND SUBSTR(a.sales_date,-8,6) >= 202108  
AND SUBSTR(a.sales_date,-8,6) <= 202202
GROUP BY to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM')
);

먼저 서브 쿼리절에 날짜를 입력 받아 처리하는 부분에서 컬럼들에 합을 구하면 된다고 생각한 것이고, 그것을 묶어 sales_date 컬럼 기준으로 묶어주면 생각한 것이에요. 그래서 위와 같이 Query 문을 작성하고, DBeaver를 통해 Test한 결과 원하는 값이 나온 것이에요.

SELECT * FROM ( 
SELECT ROWNUM AS RNUM, PL.* 
FROM ( 
SELECT * 
FROM (  
SELECT a.* 
from(
select to_char(to_date(sales_date, 'yyyyMMdd'), 'yyyy-MM') period, sum(sales_amt) AS sales, sum(pg_commission_amt) AS pg_fee, sum(company_settlement_amt) AS store_settlement_amt, sum(self_pay_coupun) AS coupon, sum(point) AS point, sum(self_goods_deli_amt) AS delivery_fee, sum(tot_settlement_amt) AS final_settlement_amt
from tbl_stat_sales_cnt a  
WHERE 1=1
AND SUBSTR(a.sales_date,-8,6) >= 202108  
AND SUBSTR(a.sales_date,-8,6) <= 202202
GROUP BY to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM')
UNION ALL
select '합계' period,  nvl(sum(a.sales_amt),0) sales,  nvl(sum(a.pg_commission_amt),0) pg_fee,  nvl(sum(a.company_settlement_amt),0) store_settlement_amt,  nvl(sum(a.self_pay_coupun),0) coupon,  nvl(sum(a.point),0) point,  nvl(sum(a.self_goods_deli_amt),0) delivery_fee,  nvl(sum(a.tot_settlement_amt),0) final_settlement_amt  
from tbl_stat_sales_cnt a  
WHERE 1=1  
AND SUBSTR(a.sales_date,-8,6) >= 202108  AND SUBSTR(a.sales_date,-8,6) <= 202202  )a  
ORDER BY (CASE WHEN a.period = '합계' THEN 2 ELSE 1 END), a.period DESC  ) 
WHERE ROWNUM <= 10 ) PL ) 
WHERE RNUM > 0;

기존에 있었던 Query문에 주니하랑이 수정한 부분을 살포시 얹어서 다시 Test를 해 본 것이에요.

역시나 원하는 값이 나오게 되었고, 이걸 Service에 들어 있는 Query문에 다시 살포시 얹어 준 것이에요.

@Slf4j
@Service("junyharangSaleDailyStatisticService")
public class junyharangSaleDailyStatisticService implements LogicRepository {

    @Autowired
    JdbcQueryFactory query;

    @Autowired
    JunyharnagoctDao junyharangoctdao;

    @Override
    public List<Map<String, Object>> list(QueryContext context) {
        Map<String,Object> data = context.getData();
        if (data == null) {
            throw new ApiException("500", "data is null");
        }

        int page = JsonUtils.getIntValue(data, "page");
        int size = JsonUtils.getIntValue(data, "pageSize");

        String period_fromto = JsonUtils.getStringValue(data, "period_fromto");
        String channel_matching = JsonUtils.getStringValue(data, "channel_matching");

        String from ="";
        String to ="";
        String sql = "";
        int term = period_fromto.length();

        if(term == 21){

            if (!StringUtils.isEmpty(period_fromto)) {
                String[] tmp = period_fromto.split("~");
                from = tmp[0].replace("-","");
                to = tmp[1].replace("-","");
            }

            sql =   " SELECT a.* from( " +
                    " select to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM-dd') period, " +
                    " a.sales_amt sales, " +
                    " a.pg_commission_amt pg_fee, " +
                    " a.company_settlement_amt store_settlement_amt, " +
                    " a.self_pay_coupun coupon, " +
                    " a.point , " +
                    " a.self_goods_deli_amt delivery_fee, " +
                    " a.tot_settlement_amt final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND a.sales_date >= "+ from +" " +
                    " AND a.sales_date <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " UNION ALL " +
                    " select '합계' period, " +
                    " nvl(sum(a.sales_amt),0) sales, " +
                    " nvl(sum(a.pg_commission_amt),0) pg_fee, " +
                    " nvl(sum(a.company_settlement_amt),0) store_settlement_amt, " +
                    " nvl(sum(a.self_pay_coupun),0) coupon, " +
                    " nvl(sum(a.point),0) point, " +
                    " nvl(sum(a.self_goods_deli_amt),0) delivery_fee, " +
                    " nvl(sum(a.tot_settlement_amt),0) final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND a.sales_date >= "+ from +" " +
                    " AND a.sales_date <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " )a " +
                    " ORDER BY (CASE WHEN a.period = '합계' THEN 2 ELSE 1 END), a.period DESC ";
        }else {

                if (!StringUtils.isEmpty(period_fromto)) {
                    String[] tmp = period_fromto.split("~");
                    from = tmp[0].replace("-","");
                    to = tmp[1].replace("-","");
                }

            sql =   " SELECT a.* from( " +
                    " select to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM-dd') period, " +
                    " sum(sales_amt) AS sales, " +
                    " sum(pg_commission_amt) AS pg_fee, " +
                    " sum(company_settlement_amt) AS store_settlement_amt, " +
                    " sum(self_pay_coupun) AS coupon, " +
                    " sum(point) AS point , " +
                    " sum(self_goods_deli_amt) AS delivery_fee, " +
                    " sum(tot_settlement_amt) AS final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND SUBSTR(a.sales_date,-8,6) >= "+ from +" " +
                    " AND SUBSTR(a.sales_date,-8,6) <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " GROUP BY to_char(to_date(a.sales_date, 'yyyyMMdd'), 'yyyy-MM') " +
  					" UNION ALL " +
                    " select '합계' period, " +
                    " nvl(sum(a.sales_amt),0) sales, " +
                    " nvl(sum(a.pg_commission_amt),0) pg_fee, " +
                    " nvl(sum(a.company_settlement_amt),0) store_settlement_amt, " +
                    " nvl(sum(a.self_pay_coupun),0) coupon, " +
                    " nvl(sum(a.point),0) point, " +
                    " nvl(sum(a.self_goods_deli_amt),0) delivery_fee, " +
                    " nvl(sum(a.tot_settlement_amt),0) final_settlement_amt " +
                    " from tbl_stat_sales_cnt a " +
                    " WHERE 1=1 " +
                    " AND SUBSTR(a.sales_date,-8,6) >= "+ from +" " +
                    " AND SUBSTR(a.sales_date,-8,6) <= "+ to +" " ;
                    if (!("".equals(channel_matching) || channel_matching.isEmpty())) {
                        sql += " AND a.ord_channel IN( "+ channel_matching +" ) ";
                    }
            sql +=  " )a " +
                    " ORDER BY (CASE WHEN a.period = '합계' THEN 2 ELSE 1 END), a.period DESC ";
        }

        log.info("sql - " + sql);
        List<Map<String, Object>> list = (List<Map<String, Object>>)query.list(sql, page, size, "zinSaleDailyStatistic").get("items");
        return list;
    }

    @Override
    public Map<String, Object> read(String id) {
        return null;
    }

    @Override
    public int create(Node node) {
        return 0;
    }

    @Override
    public int update(Node node) {
        return 0;
    }

    @Override
    public int delete(String id) {
        return 0;
    }

    private void replaceFilterList(Map<String,Object> params, String filterKey, String listKey) {
        List<String> filter = JsonUtils.getStringList(params, filterKey);
        params.put(listKey, filter);
    }
}

728x90

저작자표시 비영리 변경금지

'개념 정리 작업실 > Data Base' 카테고리의 다른 글

[H2-DB] Auto_Increment를 하고 싶지만, SQL Exception이 뜰 때 (0)	2022.01.12
[Data Base] Transaction (0)	2021.10.05

Juny's ITLog

Juny's ITLog

태그

최근글

댓글

공지사항

아카이브

🚀 Code Content

🔽 Service.java

🔽 SQL문

⚠️ 문제 발생!

🍻 문제 해결!

'개념 정리 작업실 > Data Base' 카테고리의 다른 글

관련글

티스토리툴바

Juny's ITLog

태그

최근글

댓글

공지사항

아카이브

🚀 Code Content

🔽 Service.java

🔽 SQL문

⚠️ 문제 발생!

🍻 문제 해결!

'개념 정리 작업실 > Data Base' 카테고리의 다른 글

관련글

티스토리툴바

🚀 Code Content

🔽 Service.java

🔽 SQL문

⚠️ 문제 발생!

🍻 문제 해결!