JOIN
NR == FNR 는 입력으로 두 개의 파일을 다룰 때 간단히 사용되는 방법입니다.
FNR 값은 입력 파일이 변경될 때마다 0 으로 reset 되어 다시 시작하고
NR 값은 계속해서 누적되므로 결과적으로 NR == FNR 가 되는 경우는 처음 파일에만 해당됩니다.
따라서 NR == FNR { a[$0]; next } 는 첫 번째 파일을 처리하기 위한 부분으로
레코드 값을 index 로 하는 array 를 생성합니다.
뒤에 next 가 있으므로 이후의 코드는 실행되지 않고 계속해서 다음 레코드로 이동하겠죠.
$0 in a 는 a 라는 array 에 index 값으로 $0 가 있는지 조회하는 방법이므로
결국 file1, file2 두 파일의 공통 라인에 대해서만 참이 되게 됩니다.
# 라인 111, 222, 333 은 file1, file2 공통
$ cat file1 $ cat file2
AAA XXX
111 111
222 222
BBB 333
CCC YYY
333 ZZZ
....................................................
# file1 와 file2 공통 라인 프린트 (intersection)
$ awk 'NR == FNR{ a[$1]; next } $1 in a' file1 file2
111
222
333
# file1 에만 있는 라인 프린트 (file2 file1 순서 주의)
$ awk 'NR == FNR{ a[$1]; next } !($1 in a)' file2 file1
AAA
BBB
CCC
# file2 에만 있는 라인 프린트 (file1 file2 순서 주의)
$ awk 'NR == FNR{ a[$1]; next } !($1 in a)' file1 file2
XXX
YYY
ZZZ
JOIN 은 data 파일과 map 파일에 공통적으로 존재하는 키값을 이용해 일종의 치환 작업을 합니다. map 파일에 존재하는 키값에 한에서 data 파일에서 키가 사용되면 상관없는데 그렇지 않고 map 파일에 존재하지 않는 키가 data 파일에서 사용되면 여기서 LEFT JOIN 과 INNER JOIN 의 차이가 생깁니다. LEFT JOIN 은 map 파일에 해당 키값이 없을 경우 해당 컬럼 값을 공백으로 두고 나머지 정보들을 표시하는데 반해 INNER JOIN 은 아무런 정보도 표시하지 않습니다.
$ cat data
20081010 123 xxx
20081011 234 def
20081012 512 xyz
20081013 512 abc
20081014 717 def
20081015 999 zzz <--- map 파일에는 없는 키
$ cat map
abc withdrawal
def payment
xyz deposit
xxx balance
........................................
# SELECT d.$1, d.$2, m.$2 FROM data d
# LEFT JOIN map m ON d.$3 = m.$1
$ awk 'NR == FNR{ a[$1]=$2; next } { print $1,$2,a[$3] }' map data
20081010 123 balance
20081011 234 payment
20081012 512 deposit
20081013 512 withdrawal
20081014 717 payment
20081015 999 <---- LEFT JOIN 결과
# SELECT d.$1, d.$2, m.$2 FROM data d
# INNER JOIN map m ON d.$3 = m.$1
$ awk 'NR == FNR{ a[$1]=$2; next }
$3 in a { print $1,$2,a[$3] } # INNER JOIN 을 위한 키값 체크
' map data
20081010 123 balance
20081011 234 payment
20081012 512 deposit
20081013 512 withdrawal
20081014 717 payment
앞서 사용한 data, map 파일에 이어 map2 파일을 추가하여 두 개의 파일을 JOIN 합니다.
먼저 map, map2 두 개의 파일을 array 로 읽어들여야 하는데 NR == FNR 는 첫 번째 파일에만
적용되므로 여기서는 사용할 수 없고 BEGIN 블록에서 getline 을 이용해 직접 읽어들입니다.
$ cat map2
123 spring
234 summer
512 autumn
717 winter
...............................................
# SELECT d.$1, m2.$2, m.$2 FROM data d
# LEFT JOIN map m ON d.$3 = m.$1
# LEFT JOIN map2 m2 ON d.$2 = m2.$1
$ awk 'BEGIN {
while (getline < ARGV[1] > 0) m[$1] = $2
while (getline < ARGV[2] > 0) m2[$1] = $2
while (getline < ARGV[3] > 0) {
print $1,m2[$2],m[$3]
}
}' map map2 data
20081010 spring balance
20081011 summer payment
20081012 autumn deposit
20081013 autumn withdrawal
20081014 winter payment
20081015 <---- LEFT JOIN 결과
# SELECT d.$1, m2.$2, m.$2 FROM data d
# INNER JOIN map m ON d.$3 = m.$1
# INNER JOIN map2 m2 ON d.$2 = m2.$1
$ awk 'BEGIN {
while (getline < ARGV[1] > 0) m[$1] = $2
while (getline < ARGV[2] > 0) m2[$1] = $2
while (getline < ARGV[3] > 0) {
if (( $2 in m2 ) && ( $3 in m )) { # INNER JOIN 을 위한 키값 체크
print $1,m2[$2],m[$3]
}
}
}' map map2 data
20081010 spring balance
20081011 summer payment
20081012 autumn deposit
20081013 autumn withdrawal
20081014 winter payment
GROUP BY 와 JOIN 을 함께 사용
# 사용할땐 데이터 이외의 헤더 부분은 삭제해야 합니다.
$ cat Shippers
ShipperID ShipperName Phone
----------+-----------------+---------------
1 , Speedy Express , (503) 555-9831
2 , United Package , (503) 555-3199
3 , Federal Shipping , (503) 555-9931
$ cat Orders
OrderID CustomerID EmployeeID OrderDate ShipperID
--------+-----------+----------+-----------+----------
10248 90 5 1996-07-04 3
10249 81 6 1996-07-05 1
10250 34 4 1996-07-08 2
10251 84 3 1996-07-08 1
10252 76 4 1996-07-09 2
10253 34 3 1996-07-10 2
10254 14 5 1996-07-11 2
##################### SQL 문 #######################
SELECT S.ShipperName, COUNT(O.OrderID) AS NumberOfOrders
FROM Orders O
LEFT JOIN Shippers S ON O.ShipperID = S.ShipperID
GROUP BY ShipperName
ORDER BY NumberOfOrders
ShipperName NumberOfOrders
---------------- --------------
Federal Shipping 1
Speedy Express 2
United Package 4
###################### AWK ##########################
# $1 : Shippers.ShipperID
# $2 : Shippers.ShipperName
# $5 : Orders.ShipperID
$ awk '
NR == FNR{ s[$1]=$2; next }
{ o[$5]++ }
END {
PROCINFO["sorted_in"] = "@val_num_asc"
for ( i in o ) { printf "%-20s %d\n", s[i], o[i] }
}
' FS=' *, *' Shippers FS=' ' Orders
Federal Shipping 1
Speedy Express 2
United Package 4
CROSS JOIN
CROSS JOIN 은 Cartesian product 라고도 하는데 두 집합 A, B에 대해 a∈A이고 b∈B인 모든 순서쌍(a, b)의 집합을 말합니다. 그러니까 만약에 테이블 A 에 10 개의 row 가 있고 테이블 B 에 10 개의 row 가 있으면 CROSS JOIN 을 하게 되면 모두 10 X 10 = 100 개의 row 가 생기게 됩니다. joining everything to everything 이기 때문에 따로 ON 절이 없고 만약에 테이블 하나가 empty 면 result set 은 0 이 됩니다.
이것이 어디에 필요한지 예제를 통해 알아보면 먼저 아래와 같은 테이블이 있을 경우 "각 Store 에서 판매된 Product 별로 Amount 를 SUM 해서 보여달라" 라고 하면 간단히 GROUP BY 를 하면 됩니다.
$ cat sales $ cat stores $ cat products
Store Product Amount Store Product
-------+---------+------- ----- -------
lg24 snack 300 lg24 milk
lg24 snack 200 du bread
lg24 soda 150 7ele soda
lg24 soda 250 snack
du milk 150
du bread 100
du bread 200
7ele snack 100
7ele milk 150
7ele bread 200
7ele bread 150
.......................................................................
SELECT Store, Product, SUM(Amount) as Sum
FROM sales
GROUP BY Store, Product
.........................................
$ awk '
{ a[$1,$2] += $3 }
END {
PROCINFO["sorted_in"] = "@ind_str_asc"
for ( i in a ) {
split(i, b, SUBSEP)
printf "%-10s %-10s %5d\n", b[1], b[2], a[i]
}
}' sales
7ele bread 350
7ele milk 150
7ele snack 100
du bread 300
du milk 150
lg24 snack 500
lg24 soda 400
그런데 "어떤 상점에서 어떤 품목이 판매가 안됐는지 알 수 없으니 그런 품목에 대해서는 0 으로 표시해달라" 라고 할 경우엔 모든 상점에서 모든 품목을 표시해야 하므로 이때는 CROSS JOIN 을 해야 합니다.
SELECT S.Store, P.Product
FROM stores S
CROSS JOIN products P
..........................
SELECT S.Store, P.Product, ISNULL(C.Sum,0) as Sum
FROM stores S
CROSS JOIN products P
LEFT JOIN
(SELECT Store, Product, SUM(Amount) as Sum
FROM sales
GROUP BY Store, Product) C
ON
S.Store = C.Store AND
P.Product = C.Product
................................................................
$ awk 'BEGIN {
while (getline < ARGV[1] > 0) s[$1]
while (getline < ARGV[2] > 0) p[$1]
while (getline < ARGV[3] > 0) a[$1,$2] += $3
PROCINFO["sorted_in"] = "@ind_str_asc"
for ( i in s ) { # CROSS JOIN 을 위한 이중 for 문
for ( j in p ) {
printf "%-10s %-10s %5d\n", i, j, ( a[i,j] ? a[i,j] : 0 )
}
}
}' stores products sales
7ele bread 350
7ele milk 150
7ele snack 100
7ele soda 0
du bread 300
du milk 150
du snack 0
du soda 0
lg24 bread 0
lg24 milk 0
lg24 snack 500
lg24 soda 400
FULL JOIN (exclusive)
다음은 file1, file2 의 공통부분을 제외하고 각 파일에만 존재하는 데이터를 출력합니다.
SELECT *
FROM file1 f1 FULL JOIN file f2
ON f1.value = f2.value
WHERE f1.value IS NULL OR f2.value IS NULL
..........................................
$ cat file1 $ cat file2
AAA XXX
111 111
222 222
BBB 333
CCC YYY
333
............................
$ awk 'BEGIN {
while (getline < ARGV[1] > 0) a[$1]=$1
while (getline < ARGV[2] > 0) b[$1]=$1
for (i in a) if ( !b[i] ) print i
for (i in b) if ( !a[i] ) print i
}' file1 file2
AAA
CCC
BBB
XXX
YYY
다음은 FULL JOIN 을 이용해 local directory 와 remote directory 를 비교하는 예입니다. remote 파일을 비교하는 것이므로 파일을 다운로드하지 않고 md5sum 을 이용해 비교합니다. local 과 remote 파일의 md5sum 값이 다르다는 것은 파일이 수정되었다는 의미고 아래 libtlpi.a 파일은 remote 에만 존재하는 파일이 됩니다.
이 방법은 md5sum 값만을 이용해 비교하는 것으로 동일한 내용의 파일이 있을 경우 ( 예를 들면
cp에 의해 생성된 ) 는 비교되지 않습니다. 또한 hash collision 이 있을 수 있습니다. ( md5sum 을 sha1sum 이나 이상으로 변경해보세요 )
$ awk 'BEGIN {
while (getline < ARGV[1] > 0) a[$1]=$0
while (getline < ARGV[2] > 0) b[$1]=$0
for (i in a) if ( !b[i] ) print a[i]
for (i in b) if ( !a[i] ) print b[i]
}' <( find BUILD -type f -exec md5sum {} + ) \
<( ssh user@remote 'find path/to/remote/BUILD -type f -exec md5sum {} +' )
aeabb93eddf837d8dd45cbf94d0ef7d2 BUILD/pty_master_open.d
e507a57525fabe57695683442ecde7e3 BUILD/pty_fork.o
a0cc9039df2eab09f2b145e54070a081 path/to/remote/BUILD/libtlpi.a # remote 에만 존재하는 파일
a123503825f130377d49e594e3f73504 path/to/remote/BUILD/pty_fork.o
bc2a1a57bae6472a1bb1655bd404244b path/to/remote/BUILD/pty_master_open.d
FPAT 을 이용한 JOIN
|
datafile ------------------------------------------------------------------------------ {{AA1}}'s design will serve as the basis for the planned Mars {{BB1}} rover. In December {{BB2}}, {{AA1}}'s two-year mission was extended indefinitely. As of {{AA2}} 26, 2017, {{AA1}} has been on Mars for 1738 sols (1785 total days) since landing on August 6, . . . . . . . |
|
세 개의 파일을 처리하는 경우인데 이전에 BEGIN 블록을 이용하는 방법과는 다르게 BEGINFILE 블록을 사용했습니다.
$ awk -f - <<\EOF mapfile1 mapfile2 RS='.' FPAT='{{[A-Z0-9]+}}' datafile
BEGINFILE {
if ( ARGIND < 3 ) { # 1, 2 번째 파일일 경우만 실행
while (getline < FILENAME > 0) a[$1] = $3
nextfile
}
}{
r = $0 # sub 함수의 실행결과로 $0 변수에 대입연산이
for (i=1; i<=NF; i++) { # 일어나면 필드 관련 변수들이 재설정 되므로
sub($i, a[$i], r) # r 변수에 대입해 사용합니다.
}
printf "%s", r RT
}
EOF
Curiosity's design will serve as the basis for the planned Mars 2020 rover. In
^^^^^^^^^ ^^^^
December 2012, Curiosity's two-year mission was extended indefinitely. As of
^^^^ ^^^^^^^^^
June 26, 2017, Curiosity has been on Mars for 1738 sols (1785 total days)
^^^^ ^^^^^^^^^
since landing on August 6, .......