@sogaohの技術ブログ

はじめに

ExcelやスプレッドシートのVLOOKUPでのマッチングを
手早くできるほど慣れてないのでこちらでするなり

前提

  • 環境
    • macOS Majave 10.14.3
    • VS Code 1.32.1
      • Rainbow CSV Extension
      • Excel Viewer Extension

インストール

  • go インストール
    • brew install go
        ❯ go version  
        go version go1.12.1 darwin/amd64
      
  • csvq バイナリ配置( path の効いてるところ)
    • wget https://github.com/mithrandie/csvq/releases/download/v1.9.1/csvq-v1.9.1-darwin-amd64.tar.gz
    • tar xzf csvq-v1.9.1-darwin-amd64.tar.gz
    • cd csvq-v1.9.1-darwin-amd64
    • cp -p csvq /usr/local/bin/
        ❯ csvq  
        csvq interactive shell  
        Press Ctrl+D or execute "EXIT;" to terminate this shell.
      

ちょっと使ってみた

Datas

  • users.csv

      id,name,age,tel  
      1,user1,20,01011112222  
      2,user2,21,01011113333  
      3,user3,22,01011114444  
      4,user4,23,01011115555  
      5,user5,24,01011116666  
      6,user6,25,01011117777
    
  • score.csv

      id,user_id,score,date  
      1,2,100,2019/3/12 10:00  
      2,4,200,2019/3/12 10:15  
      3,6,300,2019/3/12 10:30
    

simple SELECT

> SELECT * FROM users;  
+----+--------+-----+-------------+  
| id |  name  | age |     tel     |  
+----+--------+-----+-------------+  
| 1  | user1  | 20  | 01011112222 |  
| 2  | user2  | 21  | 01011113333 |  
| 3  | user3  | 22  | 01011114444 |  
| 4  | user4  | 23  | 01011115555 |  
| 5  | user5  | 24  | 01011116666 |  
| 6  | user6  | 25  | 01011117777 |  
+----+--------+-----+-------------+  

 > SELECT * FROM score;  
+----+---------+-------+------------------+  
| id | user_id | score |       date       |  
+----+---------+-------+------------------+  
| 1  | 2       | 100   | 2019/3/12 10:00  |  
| 2  | 4       | 200   | 2019/3/12 10:15  |  
| 3  | 6       | 300   | 2019/3/12 10:30  |  
+----+---------+-------+------------------+

simple JOIN

> SELECT u.name, u.age, s.score, s.date FROM users u JOIN score s ON u.id = s.user_id;  
+--------+-----+-------+------------------+  
|  name  | age | score |       date       |  
+--------+-----+-------+------------------+  
| user2  | 21  | 100   | 2019/3/12 10:00  |  
| user4  | 23  | 200   | 2019/3/12 10:15  |  
| user6  | 25  | 300   | 2019/3/12 10:30  |  
+--------+-----+-------+------------------+  

> SELECT u.name, u.age, s.score, s.date FROM users u LEFT JOIN score s ON u.id = s.user_id;  
+--------+-----+-------+------------------+  
|  name  | age | score |       date       |  
+--------+-----+-------+------------------+  
| user1  | 20  | NULL  |       NULL       |  
| user2  | 21  | 100   | 2019/3/12 10:00  |  
| user3  | 22  | NULL  |       NULL       |  
| user4  | 23  | 200   | 2019/3/12 10:15  |  
| user5  | 24  | NULL  |       NULL       |  
| user6  | 25  | 300   | 2019/3/12 10:30  |  
+--------+-----+-------+------------------+

CSV export (simple JOIN)

❯ csvq -f csv -o left_join.csv "SELECT u.name, u.age, s.score, s.date FROM users u LEFT JOIN score s ON u.id = s.user_id;"

name,age,score,date  
user1,20,,  
user2,21,100,2019/3/12 10:00  
user3,22,,  
user4,23,200,2019/3/12 10:15  
user5,24,,  
user6,25,300,2019/3/12 10:30

refs

この記事へのコメント

まだコメントはありません