カテゴリー: SQL

投稿日:

SQLでJSONを使う

MySQLやMaria DBでも、JSON を使える

create tableでJSON型を指定

CREATE TABLE AlphaVantage (
   t_date date NOT NULL,
   ticker VARCHAR(255) NOT NULL,
   income_stmt JSON,
   CHECK (JSON_VALID(income_stmt)),
   balance JSON,
   CHECK (JSON_VALID(balance)),
   PRIMARY KEY (t_date,ticker)
);

insert 文. json.dump

url = 'https://www.alphavantage.co/query?function=INCOME_STATEMENT～' 
r = requests.get(url)
income_data = r.json()

url = 'https://www.alphavantage.co/query?function=BALANCE_SHEET~'
r = requests.get(url)
balance_data = r.json()

try :
   sql = ('INSERT INTO AlphaVantage  (t_date, ticker, income_stmt,balance) VALUES (now(6), %s, %s,%s) ON DUPLICATE KEY UPDATE income_stmt = VALUES (income_stmt),balance=VALUES(balance);')
data = [
(ticker_name, json.dumps(income_data),json.dumps(balance_data)) 
]
cur.executemany(sql, data)

select は JSON_EXTRACTで

select JSON_EXTRACT(balance, '$.annualReports[0].totalShareholderEquity')
from AlphaVantage;
投稿日:

SQLのgroup by

SQLを扱う場合、よく使われるのが「GROUP BY」です。GROUP BYは、指定したカラムでデータをグループ化して表示するためのものです。

例えば、以下のような投稿データがあったとします。

idtitlecategory
1WordPressの使い方WordPress
2PHPの基礎PHP
3MySQLの使い方MySQL
4WordPressの使い方WordPress
5Laravelの基礎Laravel

このデータを、categoryごとにグループ化して表示するには、以下のようなSQLを書きます。

SELECT category, COUNT(id) as count FROM wp_posts GROUP BY category;

このSQLを実行すると、以下のような結果が得られます。

categorycount
WordPress2
PHP1
MySQL1
Laravel1

このように、同じカテゴリーの投稿をグループ化して、その数を計算することができます。また、GROUP BYにはさまざまな関数を組み合わせることができるため、より高度な集計を行うこともできます。

WordPressでは、データベースにアクセスするための関数が用意されており、これらを利用することで簡単にSQLを実行することができます。以下は、上記のSQLをWordPressで実行する例です。

wordpressのSql実行例
global $wpdb;

$result = $wpdb->get_results("
    SELECT category, COUNT(id) as count
    FROM {$wpdb->prefix}posts
    GROUP BY category
");

foreach ($result as $row) {
    echo "{$row->category}: {$row->count} posts<br>";
}

このように、WordPressではSQLを簡単に扱うことができます。GROUP BYを使って、データの集計や分析を行う際には、ぜひWordPressの関数を活用してみてください。

投稿日:

SQL入門

データベース

※データベースを知らない人向けに、SQLを簡単に説明する記事です。

リレーショナル・データベースでは、表形式のデータに対し、ＩＤとなる列を決め、IDの値を指定してアクセスします。

↓社員情報のテーブルです。この社員IDのような列を「主キー」と呼びます。以降では社員情報をデータベースに格納する方法を説明します。

社員ID 社員名所属部署
0001田中　ABC人事部
0002山田　CDE営業部
0003佐藤　XYZ情シス部
0004鈴木　ABC人事部
0005佐藤　CDE営業部

Oracleのマニュアルのリレーショナルデータベースの記事も参考になります

テーブルを作る

まず最初にテーブルを作ります。その名も “CREATE TABLE”。先頭で、各レコードを一意に特定できるID情報を主キーに設定します。

MySQL)

CREATE TABLE shain_table
(shain_no int NOT NULL PRIMARY KEY,
 shain_name varchar(30),
 busho varchar(20));

Oracle)

CREATE TABLE shain_table
(shain_no number NOT NULL PRIMARY KEY,
 shain_name varchar2(30),
 busho varchar2(20));

参考：Oracleマニュアル　

データを登録する

テーブルにデータを登録します。 INSERT文を使います

insert into shain_table values( 1, '田中 ABC','人事部');
insert into shain_table values( 2, '山田 CDE','人事部');
insert into shain_table values( 3, '佐藤 XYZ','情シス部');

SELECT する

SELECT 文でデータを取得します。

select * from shain_table;

| shain_no | shain_name | busho   |
|        1 | 田中 ABC   | 人事部   |
|        2 | 山田 CDE   | 人事部   |
|        3 | 佐藤 XYZ   | 情シス部|
3 rows in set (0.000 sec)

検索条件を指定する場合

select * from shain_table where shain_no =3;

| shain_no | shain_name | busho  |
|        3 | 佐藤 XYZ   | 情シス部 |
1 row in set (0.000 sec)

まあ、はじめはこんな感じです。エクセルのような表形式のデータを扱うイメージがついてくれればいいのですが