|
일하기 시작한지 한달된 초보 개발자입니다.
item_name/ item_kind/ unit / day_pri /regday
마늘 / 깐마늘 / 1kg / 8000/ 20151222
이와 같은테이블에서 올해 매달의 순별 즉 초순 중순 하순의 평균가격과
평년(5년간) 매달의 초순,중순,하순의 평균 가격을 구하는 쿼리를 아래와 같이 짰는데요,
초순 중순 하순의 합이 결국 한달의 가격과 같아야 하는데
한달의 가격과 차이가 계속 나는데 원인을 잘 모르겠습니다
틀린부분과 한달 평균가와 차이가 나는 원인을 알고 싶습니다.
더불어 수정 방향도 알려주시면 고맙구요 ㅠ
쿼리는 다음과 같습니다.
select basDate,wsalPrice,avgPrice
from(
select a.md basDate, round (a.ps) wsalPrice, round(b.ps) avgPrice
from
(SELECT substr(regday,5,2)||'월 초순' md,substr(regday,3,4)||'a' ma,avg(dailyPrice ) ps
FROM
(SELECT regday , day_pri AS dailyPrice
FROM day_kamis_pri_info
WHERE item_name = '마늘'
and item_kind ='깐마늘'
and unit = '1kg'
AND regday BETWEEN (select substr(max(regday),1,7)-20||1 from day_kamis_pri_info) AND (select max(regday) from day_kamis_pri_info)
)
where substr(regday,7,2) between '01' and '10'
GROUP BY substr(regday,5,2),substr(regday,3,4)
union all
SELECT substr(regday,5,2)||'월 중순' md,substr(regday,3,4)||'b' ma,avg(dailyPrice ) ps
FROM (SELECT regday , day_pri AS dailyPrice
FROM day_kamis_pri_info
WHERE item_name ='마늘'
and item_kind ='깐마늘'
and unit = '1kg'
AND regday BETWEEN (select substr(max(regday),1,7)-20||1 from day_kamis_pri_info) AND (select max(regday) from day_kamis_pri_info)
)
where substr(regday,7,2) between '11' and '20'
GROUP BY substr(regday,5,2),substr(regday,3,4)
union all
SELECT substr(regday,5,2)||'월 하순' md,substr(regday,3,4)||'c' ma,avg(dailyPrice ) ps
FROM (SELECT regday , day_pri AS dailyPrice
FROM day_kamis_pri_info
WHERE item_name = '마늘'
and item_kind ='깐마늘'
and unit = '1kg'
AND regday BETWEEN (select substr(max(regday),1,7)-20||1 from day_kamis_pri_info) AND (select max(regday) from day_kamis_pri_info)
)
where substr(regday,7,2) between '21' and '31'
GROUP BY substr(regday,5,2),substr(regday,3,4)
order by ma) a
,((select * from
(SELECT substr(regday,5,2)||'월 초순' md,substr(regday,5,2)||'a' ma,avg(dailyPrice ) ps
FROM (SELECT regday , day_pri AS dailyPrice
FROM day_kamis_pri_info
WHERE item_name = '마늘'
and item_kind ='깐마늘'
and unit = '1kg'
AND regday BETWEEN (select substr(max(regday),1,4)-5||0101 from day_kamis_pri_info) AND (select substr(max(regday),1,4)-1||1231 from day_kamis_pri_info)
)
where substr(regday,7,2) between '01' and '10'
GROUP BY substr(regday,5,2)
union all
SELECT substr(regday,5,2)||'월 중순' md,substr(regday,5,2)||'b' ma,avg(dailyPrice ) ps
FROM (SELECT regday , day_pri AS dailyPrice
FROM day_kamis_pri_info
WHERE item_name = '마늘'
AND regday BETWEEN (select substr(max(regday),1,4)-5||0101 from day_kamis_pri_info) AND (select substr(max(regday),1,4)-1||1231 from day_kamis_pri_info)
) where substr(regday,7,2) between '11' and '20'
GROUP BY substr(regday,5,2)
union all
SELECT substr(regday,5,2)||'월 하순' md,substr(regday,5,2)||'c' ma,avg(dailyPrice ) ps
FROM (SELECT regday , day_pri AS dailyPrice
FROM day_kamis_pri_info
WHERE item_name ='마늘'
and item_kind ='깐마늘'
and unit = '1kg'
AND regday BETWEEN (select substr(max(regday),1,4)-5||0101 from day_kamis_pri_info) AND (select substr(max(regday),1,4)-1||1231 from day_kamis_pri_info)
)
where substr(regday,7,2) between '21' and '31'
GROUP BY substr(regday,5,2)
order by ma)) ) b
where a.md=b.md)
|
