[SQL]データの縦持ち、横持ちを入れ替える | DevelopersIO
はじめに 弊社のデータ分析案件において、RedshiftにてSQLを実行してデータを抽出することが増えてまいりました。SQLはデータの抽出には強力なのですが、JavaやRubyなどのプログラミング言語と比べると独特であることも事実です。 例えば(PL/SQLなどは除いてですが) ループがない 変数に代入して次の処理に行くことができない メソッドやクラスに処理を分割できない などです。 これらの特徴からSQLでデータ抽出を行う為には、JavaやRubyなどとは異なった手法を取る必要があります。そこで「JavaやRubyとかでは・・・というやり方でやるのだけど、SQLではどうやるのだろう」というケースについて、シリーズ形式で何回かに分けて取り上げたいと思います。 尚、SQLの基本的な構文(select、join、group byなど)については説明しません。またデータベースの種類に関わらず使用
![[SQL]データの縦持ち、横持ちを入れ替える | DevelopersIO](https://cdn-ak-scissors.b.st-hatena.com/image/square/d9d7a9e19a3c5979fc0b83efa7beeec666f705bb/height=288;version=1;width=512/https%3A%2F%2Fdevio2023-media.developers.io%2Fwp-content%2Fuploads%2F2015%2F07%2Fsql.png)
PostgreSQLでSQLチューニングや障害状況調査に使ったクエリ達まとめ - $shibayu36->blog;
最近PostgreSQLでSQLチューニングや、DBが詰まった時の状況調査をいろいろやった。その時に便利だったクエリ達をまとめていく。PostgreSQLのバージョンは9.6系です。 SQLチューニングなどに便利だったクエリ達 それ以降に実行するSQLの実行時間を表示する。参考 https://morumoru00.wordpress.com/2011/05/08/postgresql-sql%E5%87%A6%E7%90%86%E6%99%82%E9%96%93%E3%82%92%E8%AA%BF%E3%81%B9%E3%82%8B%EF%BC%88timing/ \timing 実際にクエリを実行して実行計画や実行時間を表示する。クエリが実行されるので破壊的な操作も実行されてしまうことに注意。トランザクション張って最後にROLLBACKしましょう。参考 https://www.post
MacでPostgreSQLのクライアントならPosticoがかなりおすすめ
今までpgAdmin3を使っていたのですが、これが遅い、落ちるたりするのでなかなか使い勝手がよくなかったのですが、先日Posticoなるものを発見して使い出しましたが、かなり気にいっております。 SSH経由での接続ももちろん可能です Vagrantの中にあるPostgresなんかにもこれで接続できます 接続後の画面 テーブル内のデータ編集画面 SQLの画面 有料版はFilter機能もあります 詳細 サポートしてPostgreSQLのバージョン PostgreSQL 8.0, 8.4, 9.1, 9.2, 9.3 9.4、Amazon Redshiftもサポートしているようです。 Posticoはlibpq 9.3.8のパッチをつかっているそうです。 トライアル版と有料版の違い トライアル版は、お気に入りに5 connectionまでした登録できません。 トライアル版は、１画面でしかしようで
SELECT の集計結果でテーブルを UPDATE する SQL 文 - hoge256ブログ
頻繁に使うのですが、結構いつもど忘れしてしまうので、メモっておきます。 例えば、次のような得点テーブル（score_data）とユーザ得点集計テーブル（user_total）があったとして、得点テーブルの集計結果で、ユーザ得点集計テーブルをアップデートする場合の SQL 文です。 SELECT * FROM score_data; test_date | user_name | score ------------+-----------+------- 2008-01-01 | A | 85 2008-01-01 | B | 65 2008-01-01 | C | 90 2008-02-01 | A | 76 2008-02-01 | B | 58 2008-02-01 | C | 88 2008-03-01 | A | 92 2008-03-01 | B | 70 2008-03-01
LATERALを使ってみよう | Let's POSTGRES
笠原 辰仁 本記事は2015年のPostgreSQL Advent Calendarの 12/3 の記事です。PostgreSQL9.3でサポートされたLATERALについての解説と使いどころについて紹介します。 はじめに PostgreSQLは幅広くSQL標準(ではないものも含む)の句や構文をサポートしており、それが製品の特徴の一つでもあります。PostgreSQL8.4でサポートされたWindow関数やWITH句はその引き合いに出されることが多いです。 さて、PostgreSQL9.3からはLATERALという句がサポートされています。やや地味で使われることが少ないため、ご存知の方は少ないかもしれません。しかし、LATERAL句は、使いどころによっては非常に強力な武器になります。 注意 本記事ではテーブル定義や実行計画等を記載している箇所がありますが、幅の表示上、見やすいように改行して
第5回 PostgreSQLの基礎を見直す | gihyo.jp
みなさんこんにちは。技術系Q&Aサイトteratail開発チームの出川幾夫です。 PostgreSQLはMySQLと並んで広く使われているオープンソースのRDBMSです。商用のRDMBSと比較しても遜色ない高機能なRDMBSですが、その機能を特に意識せず運用してしまっている現場も多いのではないでしょうか。 今回はPostgreSQLの、普段の運用では無視しがちな、データベースをサービスに使っている人間として知っておくべき基礎的な部分について、概説していこうと思います。 PostgreSQLのデータ型・関数 PostgreSQLではさまざまなデータ型が利用可能です。文字列型はchar、varchar、textなどがあり、char(n)やvarchar(n)のnはバイト数ではなく文字数で指定します。 integer型やfloat型などの数値型にはMySQLにあるようなUNSIGNEDはないので
PostgreSQLの分析関数の衝撃2 （Lag関数と累計と移動累計）
はじめに 2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズをPostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。 本稿では、『分析関数の衝撃2 （中編）』を、PostgreSQL8.4用にリニューアルした内容を扱います。 対象読者 PostgreSQLでwindow関数を使ってみたい方 分析関数の理解を深めたい方 『自己結合の使い方』と 『相関サブクエリで行と行を比較する』に記載されているSQLをwindow関数を使って記述していきますので、『自己結合の使い方』と『相関サブクエリで行と行を比較する』を読まれてからのほうが理解しやすいと思います。 必要な環境 本稿で扱うSQLは、PostgreSQL 8.4 bet
PostgreSQLの配列を使って文字列を集約をする方法 | dTblog | デザインとプログラムの境界をさまようブログ
1対N（OneToMany）の関係にある2つのエンティティについて、N個の文字列を集約する必要があって、方法を考えてみた。 次のような部署マスタと社員マスタを例にとって、説明します。 これに対して、次のような表を出すという要件。 部署 所属社員 営業1課 「2つのエンティティを JOIN すれば良いじゃん」という声もあるけど、単純に結合しちゃうと、結果セットから取り出すとき、もしくは表示時点でプログラムでごにょごにょやらないと、うまくいかない。所属社員の一覧を、改行区切りの文字列として、ひとつの変数に格納できれば、らくちん。要するに、DBから値を取得する時点で、表示するときの形まで持っていきたいという話。 ここの方法については賛否両論あるかもしれないけど、SQLはプログラムが最もシンプルで済むような結果が得られるように書くべきだと思ってます。 話が逸れたけど、じゃあ、それをどうやってやるか
PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～
2. 自己紹介 • 氏名下雅意美紀 • 所属TIS株式会社 • 経歴入社1年目 • PostgreSQL歴＝ 入社歴 • 業務で勉強する以外にも、前回のJPUGのしくみ分科会にも 参加したり(http://thinkit.co.jp/story/2014/07/01/5074)、 PGEConsにも参加したりとコミュニティ活動なども通して日々 PostgreSQLの勉強をしています。 2 3. PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～ アジェンダ ・PostgreSQLのクエリ実行の概要 ・Explain実行結果(問い合わせプラン)の読み方 ・Explain演算子の種類 ・問い合わせプランを変更させる ・実際のデバック例 3 目標 クエリチューニングで使用するExlpainコマンドが出力する実行 計画を読めるようになりましょう。 PostgreSQLがクエリ
削除フラグのはなし
6. id name pass is_deleted 1 ryu xxx FALSE 2 ken xxx FALSE 3 honda xxx TRUE 8. id name pass is_deleted 1 ryu xxx FALSE 2 ken xxx FALSE 3 honda xxx TRUE 3 honda xxx FALSE
ActiveRecord4でこんなSQLクエリどう書くの? Arel編 | Webシステム開発／教育ソリューションのタイムインターメディア
ActiveRecord4でこんなSQLクエリどう書くの？ Merge編 では、関連先のscopeを使うことができるmergeを紹介しました。mergeを使う事で、変更に強いクエリを美しく組み立てることができました。 今回は、ActiveRecord4で書きにくいSQLクエリたちを紹介します。 書きにくいクエリとは、具体的には以下のものがあげられます。 比較演算ORlikeleft outer joinunionサブクエリexists ActiveRecordでこれらのクエリを組み立てる場合、どうしても美しくない（SQL文字列をべったり書いてしまうような）書き方となってしまいがちです。 この処理を美しく書くためには、ActiveRecordだけの機能では足りず、ActiveRecordが内部で使っているArelというライブラリを使う必要がでてきます。 今回は、Arelを使って華麗にSQLク
PostgreSQLで全角半角を区別しない問い合わせ
STEP.2 関数を作成してSQL文を簡略にする このままではSQL文を書くのが面倒なので、関数を作成してSQL文を簡略にします。 CREATE OR REPLACE FUNCTION get_nocase_text (text) RETURNS text AS $$ SELECT translate(upper($1) ,'-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ' ,'－０１２３４５６７８９ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ '); $$ LANGUAGE SQL IMMUTABLE; と関数を作成しておくと、SQL文は次のようになります。 SELECT 商品cd, 商品名 FROM t_商品台帳 WHERE get_nocase_text(商品名) LIKE 'Ａ４%'; STEP.3 式インデックスで高速にする 特定の列について
Rails SQL Injection Examples
Overview The Ruby on Rails web framework provides a library called ActiveRecord which provides an abstraction for accessing databases. This page lists many query methods and options in ActiveRecord which do not sanitize raw SQL arguments and are not intended to be called with unsafe user input. Careless use of these methods can open up code to SQL Injection exploits. The examples here do not inclu
タグ検索するならPostgreSQLで決まり！
(Last Updated On: 2018年8月13日)PostgreSQL Advent Calender 2013、13日目のエントリです。 表題の通り「タグ検索するならPostgreSQLで決まり！」です。 追記：JSONの場合はPostgreSQLのJSONB型を利用してタグ検索を行うを参照 RDBはタグが苦手 WebアプリではRDBでは取り扱いづらいデータを取り扱う事がよくあります。タグの管理・検索はその一つです。 RDBはタグ情報の管理・検索をしっかりやれますが、どちらかと言うと苦手な分野です。しかし、PostgreSQLの 配列 GIN（Generalized Inverse Index – 転置インデックス） を使うと簡単かつ高速に処理できます。 PostgreSQLを使うとタグ検索が簡単・高速に実現できますが、Googleで「タグ検索 PostgreSQL」と検索しても
Google Cloud SQL vs Datastore: 一流エンジニアへの道
去年 Google が提供を始めた「Google Cloud SQL」という RDBMS サービスを使ってみました。現在は限定プレビューとなっており無料で使えますが、6月12日より課金が始まります。 Google App Engine の開発者としては、元々備わっているキーバリュー型ストアの Datastore と、 MySQL とほぼ同等の機能を持つ Cloud SQL の2つのタイプのデータベースを選べることになりました。 そこで、簡単なテストなどを行い、Cloud SQL と Datastore の比較をしてみました。 ■速度テスト ・1レコード約1KBのデータを100件挿入した時の平均時間(20回) Cloud SQL: 0.5507秒 Datastore: 10.9612秒 ・2000レコード(約2MB)を全件読み込んだ時の平均時間(20回) Cloud SQL: 1.1001秒
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
ヤフー社内でやってるMySQLチューニングセミナー大公開
まずやっとくPostgreSQLチューニング
やっぱり SQL を書きたい派に送る jOOQ
Ruby on Rails on MySQL チューニング入門
postgresql - sql - count of `true` values