mysqlで検索結果の上位に対して、さらに条件指定

mysqlで検索結果を何らかの順序でソートした結果に対して、さらに条件を指定して検索結果を絞り込む方法です。

mysql> select * from tbl3;
+-----+------+-------+
| id | type | score |
+-----+------+-------+
| id0 | O | 0 |
| id1 | B | 2 |
| id2 | A | 4 |
| id3 | AB | 6 |
| id4 | A | 8 |
+-----+------+-------+
5 rows in set (0.00 sec)


scoreの上位3件内で、type='A'のレコードだけを抽出するには
以下のようにします。
ordにはscoreでの順位を取得しています。

mysql> set @ord := 0;
mysql>
select
t.ord
, t.id as id
, t.type as type
, t.score as score
from
(
select
@ord := @ord + 1 as ord
, id as id
, type as type
, score as score
from
tbl3
order by
id desc
limit 3
) t

where
type = 'A'
;

+------+-----+------+-------+
| ord | id | type | score |
+------+-----+------+-------+
| 1 | id4 | A | 8 |
| 3 | id2 | A | 4 |
+------+-----+------+-------+
2 rows in set (0.00 sec)

rubyでxlsファイルをtsvファイルに変換

rubyでxlsファイルをtsvファイルに変換するプログラムです。
標準入力からxlsファイルを読み込む場合には、Spreadsheet.openで直接読み込もうとするとエラーになるため、いったんTempfileに書き込んでおいて、Tempfileから読み直すようにしています。

#!/usr/local/bin/ruby
#
# usage: xls_to_tsv.rb [xls] > {tsv}
#

$KCODE = 'u'
require 'jcode'
require 'rubygems'
require 'spreadsheet'

# 引数
xls_file = nil
if ARGV.length == 0
tf = Tempfile.new('xls_to_tsv')
data = STDIN.read
tf.write(data)
tf.rewind
xls_file = tf
elsif ARGV.length == 1
xls_file = ARGV[0]
else
STDERR.print("usage: xls_to_tsv.rb [xls] > {tsv}\n")
exit(1)
end

# sheet
book = Spreadsheet.open(xls_file)
sheet = book.worksheet(0)

0.upto(sheet.row_count - 1) do |r|
tsv_values = []

0.upto(sheet.column_count - 1) do |c|
if sheet[r, c].class == Spreadsheet::Formula
value = sheet[r, c].value.to_s
elsif ! sheet[r, c]
value = ''
else
value = sheet[r, c].to_s
end

tsv_values.push(value)
end

print(tsv_values.join("\t") + "\n")
end

# 標準入力の場合
if ARGV.length == 0
xls_file.close
end

rubyでtsvファイルからxlsファイルを作成（その2）

rubyでtsvファイルからxlsファイルを生成するプログラムで、Spreadsheet::Workbook#writeで標準出力に出力しようとするとエラーになりますが、データをTempfileに出力して、Tempfileからデータを読み直して標準出力に出力することができます。


#!/usr/local/bin/ruby
#
# usage: tsv_to_xls.rb [xls] < {tsv}
#

$KCODE = 'u'
require 'jcode'
require 'rubygems'
require 'spreadsheet'

# 引数
xls_file = nil
if ARGV.length == 0
xls_file = Tempfile.new('tsv_to_xls')
elsif ARGV.length == 1
xls_file = ARGV[0]
else
STDERR.print("usage: tsv_to_xls.rb [xls] < {tsv}\n")
exit(1)
end

# sheet
book = Spreadsheet::Workbook.new
sheet = book.create_worksheet

# tsv 読み込み
STDIN.each_with_index do |line, r|
a = line.chomp.split("\t")
a.each_with_index do |value, c|
sheet[r, c] = value
end
end

# 出力
book.write(xls_file)

# 標準出力へ
if ARGV.length == 0
xls_file.rewind
data = xls_file.read
STDOUT.write(data)
end

mysqlで検索結果に順位を付与する方法

mysqlで検索結果に順位を付与した結果を取得する方法です。

以下のようなテーブルで、id順にソートした結果に順位を付与します。

mysql> select * from tbl1;
+-----+
| id |
+-----+
| id0 |
| id1 |
| id2 |
| id3 |
| id4 |
| id5 |
| id6 |
| id7 |
| id8 |
| id9 |
+-----+

mysql> set @ord := 0;
mysql> select
-> @ord := @ord + 1 as ord
-> , id as id
-> from
-> tbl1
-> order by
-> id asc
-> ;
+------+-----+
| ord | id |
+------+-----+
| 1 | id0 |
| 2 | id1 |
| 3 | id2 |
| 4 | id3 |
| 5 | id4 |
| 6 | id5 |
| 7 | id6 |
| 8 | id7 |
| 9 | id8 |
| 10 | id9 |
+------+-----+

rubyでcsvをtsvに変換

rubyでcsvを扱うのにcsvライブラリが便利です。
エスケープ処理まで考えると、単純に文字列を split(",") するわけにはいかないので。

自分的には csv よりも tsv の方が好きなので、csv ファイルは tsv にしてから処理します。
というわけで、csv ライブラリのサンプルとして tsv に変換するプログラムです。

#!/usr/local/bin/ruby

require 'csv'

CSV.open(ARGV[0], 'r') do |row|
print(row.join("\t") + "\n")
end

row は CSV::Cell の配列です。


標準入力から csv を読み込む場合には、CSV::IOReader を使います。

#!/usr/local/bin/ruby

require 'csv'

CSV::IOReader.new(STDIN).each do |row|
STDOUT.print(row.join("\t") + "\n")
end

標準入力からのSQLのmysqlコマンド実行結果

mysqlの対話モードでは以下のように、実行時間が出力されます。

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-03-04 21:45:57 |
+---------------------+
1 row in set (0.00 sec)

mysqlコマンドの標準入力にSQLを与えると、tsvで実行結果が出力されます。

$ echo "select now();" | mysql
now()
2011-03-04 21:38:46

標準入力からのSQLを実行する場合でも対話モードと同じように実行時間が出力されるようにするには、-vvvオプションを指定します。

$ echo "select now();" | mysql -vvv
--------------
select now()
--------------

+---------------------+
| now() |
+---------------------+
| 2011-03-04 21:48:49 |
+---------------------+
1 row in set (0.00 sec)

Bye

対話モードで source でSQLファイルを読み込むという手もありますが。

dateコマンドでの日付計算

dateコマンドで現在時刻からの差分を指定した日時を表示する方法の備忘録です。

$ date +'%Y-%m-%d %H:%M:%S' --date '+1 hour'
2011-03-03 00:01:16

$ date +'%Y-%m-%d %H:%M:%S' --date '-2 day'
2011-02-28 23:02:45

mysqldump での時刻条件指定

mysqldump コマンドの -w オプションに now() で現在日時を指定すると、now() の現在日時が UTC になる場合があります。

■datetime型のカラムを持つテーブルを作成し、now()で現在日時のデータを登録

mysql> create table tbl (t datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbl set t = now();
Query OK, 1 row affected (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-03-01 23:06:01 |
+---------------------+
1 row in set (0.00 sec)


■mysqldump で now() 以下のデータをダンプ

$ mysqldump -u tech test_nikeda tbl -w "t <= now()"
-- MySQL dump 10.11
--
-- Host: localhost Database: test_nikeda
-- ------------------------------------------------------
-- Server version 5.0.77-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tbl`
--

DROP TABLE IF EXISTS `tbl`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tbl` (
`t` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `tbl`
--
-- WHERE: t <= now()

LOCK TABLES `tbl` WRITE;
/*!40000 ALTER TABLE `tbl` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-03-01 14:07:20


insert文が出力されていません。
mysqldump の最後に出力されている時刻はUTCの時刻なので、now() が 9時間前の時刻になっているのでしょう。

now() ではなく、直接時刻を設定すれば問題ありません。

$ mysqldump db tbl -w "t <= '2011-03-01 24:00:00'"
...
--
-- Dumping data for table `tbl`
--
-- WHERE: t <= '2011-03-01 24:00:00'

LOCK TABLES `tbl` WRITE;
/*!40000 ALTER TABLE `tbl` DISABLE KEYS */;
INSERT INTO `tbl` VALUES ('2011-03-01 23:05:55');
/*!40000 ALTER TABLE `tbl` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
...