カテゴリー「database / SQL / O-R mapping」の5件の記事

2014.02.18

SQLで文字列の出現回数を調べる

この記事は、SQLで文字列の出現回数を調べる - 銀の弾丸に加筆修正のうえ転載しました。


朝7時、SQLでテーブル内の文字列フィールドから、特定キーワードの出現回数を得るやり方を知って、ちょっと興奮。家庭も仕事も結構切羽詰って忙しいのに朝っぱらからなにやってるんだかと呆れもするが、ゼヒ御紹介させてもらいます。

/*
 * [MySQL]
 * テーブルtblのカラムcolから、
 * キーワード '自転車' の
 * 出現回数を表示する。
 */
SET @keyword:='自転車'
SELECT
  (LENGTH(col)
    - LENGTH(REPLACE(col, @keyword, '')))
      / LENGTH(@keyword) AS CNT
FROM tbl

目からウロコ、鼻からボタモチ。

全体の文字数LENGTH(col)から、キーワードを空白に置換した後の文字数LENGTH(REPLACE(col, @keyword, ''))を引いて、キーワードの文字数LENGTH(@keyword)で割れば、確かに出現回数の出来上がり。

これは気がつかなかった。面白いねー。

[改訂第4版]SQLポケットリファレンス
朝井 淳
技術評論社
売り上げランキング: 21,343


文字列の出現回数を調べる - パンプキンスパイスラテより。

2012.02.21

MySQLのDISTINCTとCONCATで日本語文字列が変になる。

mysql(5.1)で、以下のSQLで末尾の「年」が表示されなくて困ってた。

何が悪いんだかわけわからん。

SELECT DISTINCT
  CONCAT(
    YEAR(date_begin),
    '年') as y
FROM table

いろいろ複雑な事情があるのでしょう。DISTINCTしなかったり、YEARを使わなかったり、'年'を'NEN'に変えてみるとおかしくならない。ちなみにYEARじゃなくってMONTHでもダメ。場合によっては「SYNTAX ERROR」が出たり。

しばらく困っていたけど、解決法は単純。なんてことはない、明示的にCASTするだけ(↓)

SELECT DISTINCT
  CONCAT(
    CAST(YEAR(date_begin) AS CHAR),
    '年') as y
FROM table

暗黙のキャストが行われるとうまく行かないってことかもしれんが、DISTINCTとの関係は不明なまま・・・。なにより、このバグっぽい挙動に困ってた人が、自分だけっぽくて孤独・・・

2012.02.02

SQLで月初めの日付を生成(1年分)

月毎の集計表で12か月分の各月の日付が必要で、以下のようなSQLを書いてみた(MySQL用です)。よく知ってる人からすれば当たり前の処理かもしれないけど、自分はすっと出てこないほうで、じっくり考えたから、とりあえずメモ。こういうのは「慣れ」なのかな。

過去に、安易にループしたりして、ソースコード的にも速度的にもえらい事になってるシステムを何度か見たことがある。

/*
 * 当月から過去12か月分の各月1日の
 * 日付を選択(当月含む)
 */
SELECT B.d - INTERVAL N.n MONTH AS d
FROM (
  SELECT DATE(CONCAT(
    YEAR(current_date),'-',
    MONTH(current_date),'-',
    1)) as d) AS B
  JOIN (
    SELECT 0 AS n UNION SELECT 1
    UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5
    UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9
    UNION SELECT 10 UNION SELECT 11
  ) AS N
ORDER BY d

0から11までの数列を作る部分はもうちょっとスマートに出来るのかもしれないけれど、ぱっと見てわかりやすいのはこれかなーと。

それから、一旦文字列化して日付に直しているところもスッキリしない。なんか良い手があるなら教えて欲しいです。

以下はこれをモディファイしたもので、年度内の各月を選択(年度が4月始まりの場合)。いつか使うケースがあるかもしれないので、これもメモ

/*
 * 当年度の各月1日の日付を選択
 * (4月始まりの場合)
 */
SELECT (B.d + INTERVAL N.n MONTH) AS d
FROM (
  SELECT DATE(CONCAT(
    YEAR(
      current_date - 
        INTERVAL (4-1) MONTH),'-',
        1,'-',1)) + 
        INTERVAL (4-1) MONTH as d) AS B
  JOIN (
    SELECT 0 AS n UNION SELECT 1
    UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5
    UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9
    UNION SELECT 10 UNION SELECT 11
  ) AS N
ORDER BY d

↑この本、面白そう。

2012.01.27

fetchAllとexecute

DoctrineでSELECT文を実行するのに、無意識にexecuteしてしまっていて、2回目のforeachが回ってくれないという現象にやられました。

$sql="SELECT foo,bar FROM hogeHoge ...";
$con = Doctrine_Manager::connection();
$this->record_list = $con->execute($sql);
                            //↑ここ
foreach($this->record_list as $record) {
    //一回目のループはOK
}
foreach($this->record_list as $record) {
    //二回目はループしない
}

一回のループで済ませるか、fetchAllに変えればOKですね。

実際には一回目のループはオートロードのクラス側で、二回目のループはsymfonyのテンプレート側に書いていました。

それに、「なんで配列がforeachで回らないのか?」という暗澹たる思いにとらわれていましたけど、あとでfetchAllexecuteの戻り値の型を調べてみますと、fetchAllの戻り値は配列ですが、executeの戻り値はPDOStatementというクラスオブジェクトでした。PHPってクラスオブジェクトもforeachにかけられるのですね。

いろんな条件が重なって気づくのが遅れたともいえるなー。

ドクトリンの日本語の本てほとんど無いですねー。
↓これくらいかw

2011.06.10

postgreSQL で table owner 一括変更

postgreSQLで、100個ほどあるテーブルのオーナーを変えなきゃならんかったのだが、pgAdminではまとめて変える方法をよう見つけんかった。

仕方がないので一個ずつチクチクちまちまとやりはじめたのだけど、案の定最初の数件で気が遠くなった。

以下のSQLの結果をファイルにエクスポートしてからそいつをさらに実行してみたら、とりあえずうまく行ったので覚え書き。


    SELECT
        'ALTER TABLE ' || schemaname 
        || '.' || tablename || 
        ' OWNER TO new_owner;'
    FROM pg_tables 
    WHERE tableowner ='old_owner'

ついでに各テーブルが使用しているシーケンスのオーナーも変わっていたのでうれしかった。

ちなみに調べたり試行錯誤してる時間を含めると、ちまちまやるのと時間的には大差なかった(笑)。が、まー精神的には楽やったかと。もっといいやり方あるのかも・・・。

2017年5月
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      
フォト

Google AdSense

銀の弾丸

無料ブログはココログ