제철농산물 정보 분석 및 PL/SQL 구현

[7기_김건태]

포트폴리오

(SQL 포트폴리오) 제철농산물 정보 분석 및 PLSQL 구현_김건태.docx

(SQL 포트폴리오) 제철농산물 정보 분석 및 PLSQL 구현_김건태.pdf

참고자료(출처.농림축산식품 공공데이터포털, 국립농산물품질관리원)

제철 농산물 상세정보_20180510.csv

국립농산물품질관리원 스타팜지정 현황(2017년11월28일.csv

1.목표설정

목표1.
4계절 내의 제철 농산특산물을 선정하여 산지를 알아보고,
농산특산물에 대한 각 지역의 소비자가격에 대해 SQL을 통해 분석한다.

목표2.
사용자가 검색하고자 하는 지역의 농산특산물은 무엇인지 PL/SQL을 통해 구현한다.  
¢ 농산특산물이 무엇인지 제공하되, 다음의 정보를 함께 출력한다.
-해당 지역의 농산특산물을 생산하는 우수농가의 정보.
-해당 특산물의 제철 기간. (달 수로 표기)
-해당 특산물 제철 기간의 최근 2년 가격.  
¢ 가격은 평균가격으로 다음의 세부 항목을 통해 제공한다.
-전국 전통시장의 소매가
-전국 마트의 소매가
-해당 지역 전통시장의 소매가
-해당 지역 마트의 소매가 

1.테이블 생성스크립트

CREATE TABLE food_months

 (food_name VARCHAR2(200), ---> 농산특산물 이름
food_month VARCHAR2(100), ---> 농산특산물 제철 기간
food_classify VARCHAR2(200), ---> 농산특산물 분류
food_resource VARCHAR2(300), ---> 농산특산물 산지
food_price1 VARCHAR2(100), ---> 전국 도매가 평균
food_price2 VARCHAR2(100), ---> 전국 소매가 전통시장 평균
food_price2_1 VARCHAR2(100), ---> 전국 소매가 마트 평균
food_price3 VARCHAR2(100), ---> 해당지역 소매가 전통시장 평균
food_price3_1 VARCHAR2(100)); ---> 해당지역 소매가 마트 평균

CREATE TABLE star_farm

 (farm_name VARCHAR2(200), ---> 우수농가 이름 
food_name VARCHAR2(200), ---> 농산특산물 이름
area VARCHAR2(200), ---> 우수농가 지역
address VARCHAR2(1000), ---> 우수농가 지역 주소
standard VARCHAR2(200), ---> 기준 날짜
certify VARCHAR2(200)); ---> 우수농가 인증기준

CREATE TABLE food_price

(inves_date VARCHAR2(200), ---> 조사기준 날짜
price_standard VARCHAR2(200), ---> 가격 기준
food_name  VARCHAR2(200), ---> 조사대상 농산물 이름
grade VARCHAR2(200), ---> 조사대상 농산물 품질 구분         
unit VARCHAR2(200), ---> 조사대상 농산물 기준 단위
price NUMBER(30,10), ---> 가격
area VARCHAR2(200),      ---> 조사 기준 지역
market_name VARCHAR2(1000)); ---> 조사 기준 전통시장/마트

2.분석쿼리

¢ 앞서 생성한 food_months 테이블.

Select e.*
from(Select food_month "제철기간",food_name "농산특산물", food_resource "산지",
     case when food_month = '1월' then '겨울'
          when food_month = '4월' then '봄'
          When food_month = '7월' then '여름'
          When food_month = '8월' then '가을' end 계절
     From food_months)e
     where 제철기간 in ('1월','4월','7월','8월')
     and 농산특산물 in ('감귤','마늘','감자','복숭아');

초기에 선택한 제철기간과 농산특산물의 산지가 출력되는 것을 확인한 뒤,
그를 토대로 해당 산지의 우수농가와 인증기준에 대해 출력함.
 
Select f.food_name "농산특산물",f.food_resource "산지",
      s.farm_name "우수농가", s.ADDRESS "상세주소",s.certify "인증기준"
From food_months f, star_farm s
     Where f.food_name = s.food_name
     and s.food_name in ('감귤','마늘','감자','복숭아');



출력된 결과를 통해
-감귤을 생산하는 우수농가는 7건
-마늘을 생산하는 우수농가는 1건
-복숭아를 생산하는 우수농가는 3건
-감자를 생산하는 우수농가는 0건으로 확인할 수 있었다.

도출된 결과를 통해, 감귤의 경우처럼 산지와 우수농가의 위치가 동일한 경우도 있지만,
마늘과 복숭아의 경우처럼 산지와 우수농가의 위치가 상이한 경우도 있으며, 감자의 경우 작물의 산지는 확인할 수 있었지만, 우수농가가 없는 경우도 확인할 수 있었다.
(*우수농가는 STAR FARM 이라는 이름으로 국립농산물품질관리원에 의해 지정. 2017.09 기준)

2-1.두번째 분석쿼리

 전통시장과 마트를 구분하여 가격을 비교분석 하기 위해 전통시장과 마트의 가격에 대한 View를 생성하였다.
Create view food_price_market
  as select*
  from food_price
  where market_name like ‘%시장’;

Create view food_price_mart
  as select*
  from food_price
  where market_name not like ‘%시장%’;

1.감귤에 대한 전통시장 가격 분석

Select inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
Dense_rank() over( order by price desc) 순위
       from food_price_market
       where food_name = '감귤';

감귤의 제철 기간인 1월 한달 간 전국 전통시장의 평균 가격은 3,004원이다. (10개 기준)
조사 기간 내 가장 가격이 높은 곳은 강원 강릉중앙시장으로 3,700원으로 조사됐다.
가장 낮은 가격은 경북 포항시의 죽도시장으로 2,130원으로 조사되었고, 감귤의 산지인
제주도동문시장의 가격은 2,830원으로 조사됐다.

Select inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", to_char(price,’999,999’) "가격", area "위치",
market_name "전통시장/마트 이름", Dense_rank() over(partition by area
                                                  order by price desc) 순위
            from food_price_market
            where food_name = '감귤';

지역별로 순위 결과를 나타내었을 때, 1위를 차지한 강릉시장의 경우 가장 낮은 가격이 3,140원이었고, 가장 낮은 순위를 차지한 포항 죽도시장의 경우 가장 높았던 가격이 3,060원으로 조사기간 내 강릉시장의 최저 가격보다 80원 낮은 것으로 확인되었다.

.

2.감귤에 대한 마트 가격 분석

Select inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", to_char(price,’999,999’) "가격", area "위치",
market_name "전통시장/마트 이름",
Dense_rank() over( order by price desc) 순위
  from food_price_mart
  where food_name = '감귤';





감귤의 제철 기간인 1월 한달 간 전국 마트의 평균 가격은 3,066원이다. (10개 기준)
조사 기간 내 가장 가격이 높은 곳은 제주시의 이마트 신제주점으로 5,780원으로 조사됐다.
가장 낮은 가격은 서울 이마트 수색점으로 2,280원으로 조사됐다. 조사 기간별로 가격의 변동이 있는 편인데, 제주의 경우 가장 높은 가격을 기록한 조사기간이 1월 26일과 1월 31일로, 서울 이마트 수색점의 조사기간이 1월 24일, 1월 25일인 것과 기간상의 차이가 없다는 점을 감안할 때 원산지로부터 가장 가까운 곳이 가장 가격이 높고, 반대로 가장 먼 곳이 가장 가격이 낮은
아이러니한 결과가 도출되었다.

2-2. 3번쨰 분석쿼리

분석 결과를 토대로 한 대도시와 중도시간의 가격비교.
가격 데이터 조사기준으로 선정된 대도시(서울,인천,대전,대구,광주,울산,부산,제주)와
중도시(수원,춘천,강릉,청주,창원,포항,순천) 간 전통시장과 마트의 가격을 비교하였다.

대도시

SELECT inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
DENSE_RANK() OVER(PARTITION BY area
                    ORDER BY price deSC) 순위
FROM FOOD_PRICE_MARKET
Where food_name IN ('감귤','마늘','감자','복숭아')
AND area IN ('서울','인천','대구','부산','울산','대전','광주','제주도');

뷰 생성 (전통시장)

Create view food_price_all
as SELECT inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
DENSE_RANK() OVER(PARTITION BY area
                    ORDER BY price deSC) 순위

FROM FOOD_PRICE_MARKET
WHERE food_name IN ('감귤','마늘','감자','복숭아')
AND area IN ('서울','인천','대구','부산','울산','대전','광주','제주도');

뷰 생성 (마트)

Create view food_price_all2
as SELECT inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
DENSE_RANK() OVER(PARTITION BY area
                    ORDER BY price deSC) 순위
FROM FOOD_PRICE_MART
WHERE food_name IN ('감귤','마늘','감자','복숭아')
AND area IN ('서울','인천','대구','부산','울산','대전','광주','제주도');

중도시

SELECT inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
DENSE_RANK() OVER(PARTITION BY area
                    ORDER BY price deSC) 순위
FROM FOOD_PRICE_MARKET
Where food_name IN ('감귤','마늘','감자','복숭아')
AND area not IN ('서울','인천','대구','부산','울산','대전','광주','제주도');

뷰 생성 (전통시장)

Create view food_price_all3
as SELECT inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
DENSE_RANK() OVER(PARTITION BY area
                    ORDER BY price deSC) 순위
FROM FOOD_PRICE_MARKET
WHERE food_name IN ('감귤','마늘','감자','복숭아')
AND area not IN ('서울','인천','대구','부산','울산','대전','광주','제주도');

뷰 생성 (마트)

Create view food_price_all4
as SELECT inves_date "조사기간",price_standard "가격기준", food_name "농산특산물",
grade "등급", unit "단위", price "가격", area "위치", market_name "전통시장/마트 이름",
DENSE_RANK() OVER(PARTITION BY area
                    ORDER BY price deSC) 순위
FROM FOOD_PRICE_MARKET
WHERE food_name IN ('감귤','마늘','감자','복숭아')
AND area not IN ('서울','인천','대구','부산','울산','대전','광주','제주도');

SELECT ROUND(AVG(가격))
FROM food_price_all
WHERE 농산특산물  = '감귤';

SELECT ROUND(AVG(가격))
      FROM food_price_all
      WHERE 농산특산물  = '마늘';

SELECT ROUND(AVG(가격))
      FROM food_price_all
      WHERE 농산특산물  = '감자';

SELECT ROUND(AVG(가격))
      FROM food_price_all
      WHERE 농산특산물  = '복숭아';

분석결과 감귤은 ‘부산 부전시장’, 마늘은 '광주 양동시장'에서, 감자는 '포항 죽도시장' 복숭아는 '홈플러스 순천점'에서 가장 저렴하다는 결과를 도출했다.

  결과를 통해 4개의 표본에서 2개의 표본씩 전통시장과 마트가 서로 번갈아 가며 우세한 양상을 보였고, 4개의 표본 중 3개의 항목에 대해 전통시장의 경우 중도시가 가격이 높았고

마트의 경우 대도시가 2개의 항목에 대해 가격이 높고 나머지는 같거나 중도시가 높았다.
농산물의 가격 형성에는 다양한 변인 인자가 존재하지만, 주어진 데이터로 분석하였을 때,
'마트의 가격이 높고, 대도시의 물가가 비쌀 것'이라는 고정관념이 실제와는 거리가 있다는 것을 확인하였다.

3.한계 및 의의

  전국에 분포되어 있는 제철농산물에 대해 파악하면서, 그를 생산하는 우수 농가에 대한 정보와 함께 가격을 제시하면서 합리적이고 실용적인 농산물 구입을 꾀하기 위해 분석 주제를 선정하였다. 이번 주제에 활용한 제철농산물, 전국 농산물 도소매가, 우수농가 ‘Star-farm’ 정보 데이터는 궁극적으로 농림축산식품부에서 제공된 것이다. 그러나 한 기관에서 발행했음에도 데이터 간의 공통으로 공유되는 정보가 상이하거나 (제철 농산물 산지로 기재되어있으나, 우수농가 데이터에는 누락되어있음) 가격정보의 경우 조사원의 직접방문을 통해 진행되다 보니, 누락된 달이 있거나 농산물이 추가되었다가 사라지는 경우가 있어 데이터의 신뢰성을 보기 어려웠다. 또한 농산물 가격의 변동에 영향을 변인 인자를 정확히 파악하지 못했기 때문에 정확한 분석보다 주어진 데이터를 활용해 SQL 및 PL/SQL을 통해 데이터를 가공하여 결과를 얻었다는데 의의가 있다.

4.연습문제

사용자가 검색하고자 하는 지역의 농산특산물이 무엇인지 그리고 그에 담긴 정보를 PL/SQL을 통해 구현해보자

.

set serveroutput on

set verify off

accept p_food_resource prompt '원하는 지역을 입력하세요 ';

declare

v_food_resource food_months.food_resource%type := '&p_food_resource';

v_cnt number(10);

v_exception exception;

begin

select count(*) into v_cnt

       from food_months

  where food_resource = v_food_resource;

if v_cnt = 0 then

raise v_exception;

else

for food_months_record in (select f.food_name,f.food_month,
s.farm_name, s.area,s.address,
f.food_resource,f.food_price2,
f.food_price2_1,f.food_price3,f.food_price3_1

from food_months f, star_farm s

where food_resource = '&p_food_resource'

       and f.food_name = s.food_name

and s.area = '&p_food_resource')loop

  dbms_output.put_line('해당 지역의 특산물은 '||'"'||food_months_record.food_name||'"'||'
  입니다.');
  dbms_output.put_line('해당 지역의 우수농가는 '||''''||food_months_record.farm_name||

  '('||food_months_record.address||')'||''''||' 입니다.');

  dbms_output.put_line('해당 특산물의 제철은 '||''''||food_months_record.food_month||''''||' 입니다.');

  dbms_output.put_line('해당 특산물의 제철인 '||''''||food_months_record.food_month||''''||' 의 최근 2년 가격은 다음과 같습니다.');

dbms_output.put_line('전국시장 평균기준 소매가는  '||''''||food_months_record.food_price2||'원'||''''||'이고,');

dbms_output.put_line('전국마트 평균기준 소매가는  '||''''||food_months_record.food_price2_1||'원'||''''|| '입니다.');

dbms_output.put_line(''''||'&p_food_resource'||''''||'의 시장 평균기준 소매가는 '||''''||

food_months_record.food_price3||'원'||''''||'입니다.');

end loop;

end if;

exception

 when v_exception then

  dbms_output.put_line(''''||'해당 지역의 특산물은 아쉽지만 없습니다'||'''');

 end;

/

[7기_김지우]

우수농장의 순위는 혹시 그 품목이 팔린 횟수로 따지는건지 아니면 다른 기준으로 우수 농장을 선정하는건지 궁금합니다 .

[7기_김건태]

임의로 선정되었는데, 우수농장 선정 기준 데이터, 우수농장의 판매율에 대한 데이터를 확보한다면 명확한 기준을 정할 수 있다고 생각합니다.

[7기_김건태]

두번째 분석쿼리에서 서울과 제주도 마트의 감귤 가격 이슈는 2가지의 이유가 있는 것으로 현수의 말을 빌어 적는다면
1.원산지에서 농산특산물의 가격이 더 높게 측정되는 경향이 있다. 지역에서 특산물로 선정하여 가격을 더 높게 치기 때문.
2.농산물의 신선도가 가격 형성에 영향을 미치는 요소라고 한다면, 반대로 제주는 가장 거리가 가까워 신선도가 높고 서울은 거리가 멀어 신선도가 낮다. 이 때문에 제주는 높고, 서울은 낮다고 볼 수 있다.