投稿者: StoneHills

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」です。GROUP BYは、指定したカラムでデータをグループ化して表示するためのものです。

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

id	title	category
1	WordPressの使い方	WordPress
2	PHPの基礎	PHP
3	MySQLの使い方	MySQL
4	WordPressの使い方	WordPress
5	Laravelの基礎	Laravel

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

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

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

category	count
WordPress	2
PHP	1
MySQL	1
Laravel	1

このように、同じカテゴリーの投稿をグループ化して、その数を計算することができます。また、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を簡単に説明する記事です。

リレーショナル・データベースでは、表形式のデータに対し、ＩＤとなる列を決め、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)

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