Subquery
코드에서 사용되는 데이터 파일 내용은 페이지 아래쪽에 있습니다.
Subquery 는 if ( idx in array ) 를 이용하여 구현할 수 있습니다.
##################### SQL 문 #######################
SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
FROM orders
WHERE agent_code IN (
SELECT agent_code FROM agents
WHERE working_area='Bangalore');
ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CODE
---------- ---------- --------- ---------- ----------
200130 2500 30-JUL-08 C00025 A011
200105 2500 18-JUL-08 C00025 A011
200117 800 20-OCT-08 C00014 A001
200124 500 20-JUN-08 C00017 A007
200112 2000 30-MAY-08 C00016 A007
###################### AWK ##########################
# $3 : agents.working_area
# $1 : agents.agent_code
# $6 : orders.agent_code
$ awk '
NR == FNR { if ($3 == "Bangalore") ac[$1]; next }
{
if ( $6 in ac )
printf "%d %6d %11s %8s %6s\n", $1,$2,$4,$5,$6
}
' FS=' *, *' agents FS=' ' orders
200130 2500 30-JUL-08 C00025 A011
200105 2500 18-JUL-08 C00025 A011
200117 800 20-OCT-08 C00014 A001
200124 500 20-JUN-08 C00017 A007
200112 2000 30-MAY-08 C00016 A007
Multiple Column Subquery
SELECT ord_num, agent_code, ord_date, ord_amount
FROM orders
WHERE (agent_code, ord_amount) IN
(SELECT agent_code, MIN(ord_amount)
FROM orders
GROUP BY agent_code)
ORDER BY agent_code
ORD_NUM AGENT_CODE ORD_DATE ORD_AMOUNT
-------- ---------- --------- ----------
200117 A001 20-OCT-08 800
200120 A002 20-JUL-08 500
200126 A002 24-JUN-08 500
200123 A002 16-SEP-08 500
.....
.....
###################### AWK ##########################
# $6 : orders.agent_code
# $2 : orders.ord_amount
$ awk -f - <<\EOF orders orders
NR == FNR {
if (ac[$6]) {
ac[$6] = ( $2 < ac[$6] ? $2 : ac[$6] ) # MIN(ord_amount) 을 위한 부분
} else { ac[$6] = $2 }
next
}{
if (($6 in ac) && (ac[$6] == $2))
# 출력때 $6 가 group by 되는 것을 방지하기 위해 i++ 를 추가
r[$6,i++] = sprintf("%d %6s %11s %6d", $1, $6, $4, $2)
}
END {
delete ac
PROCINFO["sorted_in"] = "@ind_str_asc"
for ( i in r ) print r[i]
}
EOF
200117 A001 20-OCT-08 800
200120 A002 20-JUL-08 500
200126 A002 24-JUN-08 500
200123 A002 16-SEP-08 500
200100 A003 08-JAN-08 1000
200121 A004 23-SEP-08 1500
200104 A004 13-MAR-08 1500
200103 A005 15-MAY-08 1500
200118 A006 20-JUL-08 500
200124 A007 20-JUN-08 500
200111 A008 10-JUL-08 1000
200116 A009 13-JUL-08 500
200135 A010 16-SEP-08 2000
200130 A011 30-JUL-08 2500
200105 A011 18-JUL-08 2500
200131 A012 26-AUG-08 900
AVERAGE
평균값을 구하기 위해 ac[$6]["AS"] 에는 sum 값이 설정되고
ac[$6]["AC"] 에는 count 값이 설정됩니다.
ORDER BY DESC 절을 구현하기 위해 compare() 함수에서 등호로 < 대신 > 가 사용되었습니다.
SELECT agent_code, AVG(ord_amount) AS average
FROM orders
GROUP BY agent_code
ORDER BY average DESC
###################### AWK ##########################
# $6 : orders.agent_code
# $2 : orders.ord_amount
$ awk -f - <<\EOF orders
{
ac[$6]["AS"] += $2
ac[$6]["AC"]++
}
END {
PROCINFO["sorted_in"] = "compare"
for ( i in ac ) printf "%s %10d\n", i, ac[i]["AS"] / ac[i]["AC"]
}
function compare(i1, v1, i2, v2, a1, a2)
{
a1 = v1["AS"] / v1["AC"]
a2 = v2["AS"] / v2["AC"]
if ( a1 > a2 ) return -1 # ORDER BY DESC 를 위해 '>' 사용
else if ( a1 == a2 ) return 0
else return 1
}
EOF
A010 3400
A005 2566
A011 2500
A008 2500
A004 2375
A002 1814
A003 1750
A006 1500
A012 1450
A007 1250
A001 800
A009 500
Data Files
awk 에서 사용할 때는 헤더 부분을 제외하고 사용하시면 됩니다.
$ cat agents
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION PHONE_NO
+------------+--------------+-----------------+------------+-----------------+
A007 , Ramasundar , Bangalore , 0.15 , 077-25814763
A003 , Alex , London , 0.13 , 075-12458969
A008 , Alford , New York , 0.12 , 044-25874365
A011 , Ravi Kumar , Bangalore , 0.15 , 077-45625874
A010 , Santakumar , Chennai , 0.14 , 007-22388644
A012 , Lucida , San Jose , 0.12 , 044-52981425
A005 , Anderson , Brisban , 0.13 , 045-21447739
A001 , Subbarao , Bangalore , 0.14 , 077-12346674
A002 , Mukesh , Mumbai , 0.11 , 029-12358964
A006 , McDen , London , 0.15 , 078-22255588
A004 , Ivan , Torento , 0.15 , 008-22544166
A009 , Benjamin , Hampshair , 0.11 , 008-22536178
$ cat orders
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE
---------- ---------- -------------- --------- --------------- ------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012