python_analytics

主にpythonやライブラリーを使ったデータ解析、機械学習、統計学などについて書いていきます

SQL の基本的な操作について

SQLの基本的な操作について

--データベース作成--

sqlite3 データベース名

sqlite3 myfriend.sqlite3

②--CSV インポート方法--

.separator ,
.import sample22.csv page1

③--接続していたデータベースから切断--

.exit

④テーブル一覧表示

.tables  

スキーマの表示

.tables 
customer_id store_id firtst_name last_name email adress_id
54 1 TERESA ROGERS TERESA.ROGERS@sakilacustomer.org 58
72 2 THERESA WATSON THERESA.WATSON@sakilacustomer.org 76
75 2 TAMMY SANDERS TAMMY.SANDERS@sakilacustomer.org 79
92 2 TINA SIMMONS TINA.SIMMONS@sakilacustomer.org 96
108 1 TRACY COLE TRACY.COLE@sakilacustomer.org 112
110 2 TIFFANY JORDAN TIFFANY.JORDAN@sakilacustomer.org 114
122 1 THELMA MURRAY THELMA.MURRAY@sakilacustomer.org 126
203 1 TARA RYAN TARA.RYAN@sakilacustomer.org 207
206 1 TERRI VASQUEZ TERRI.VASQUEZ@sakilacustomer.org 210

フィルタ条件について 例:Tを含むFirest_nameのフィルタ

select * from customer where first_name    like 'T%';

重複のみを抽出する

select * from テーブル名
where 列名 in
(select 列名 from テーブル名group by 列名
having count(*) > 1)
order by 列名 desc;

一時的にテーブルを作成する。

WITH CTE1 AS (
    SELECT 列, 列 FROM テーブル
  ), CTE2 AS (
    SELECT ID, title FROM テーブル名
  )
SELECT * FROM CTE1
UNION ALL
SELECT * FROM CTE2;
WITH cte1 AS (SELECT * FROM テーブル名)
   , cte2 AS (SELECT * FROM テーブル名)
SELECt * FROM cte2;

重複なしで結合

select
page3.ID,
page3.title
from
day
inner join
page3
on page3.ID = day.ID;

条件式で値を追加する。

select
ID,count(*),
case 
 when count(*) <5 
 then "5以下"
 when count(*) >6 
 then "6以上"
 else "その他"
end as ranking
from
page1
group by ID
;

ユニークIDのカウント

select
ID, count(*)
from
page1
group by ID
having count(*)>5
order by count(*) desc
;

dbを接続する

conn = sqlite3.connect('myfriend')
cursor = conn.cursor()

pythonでsqlite3のテーブル一覧を表示させる。

pd.io.sql.read_sql_query('select * from {table}'.format(table='page3'), conn)

グループ化したものをカウントする

SELECT visit,month, count(visit) FROM (SELECT month,ID,COUNT(month) as visit FROM fb_log GROUP by ID) as a GROUP BY visit,month;

文字列分割でカラム追加

select ID,ページ タイトル,ページ,substr(時間帯, 1, 4) as 年,substr(時間帯, 5, 2) as 月,substr(時間帯, 7, 2) as 日,substr(時間帯, 9, 2) asfrom tokushu5_1;

含むページで勝てカテゴリ列追加でフラグ立て

select  *,
case 
when page like "%aa%" then 'aa' 
when page like "bb%" then 'bb' 
when page like "%cc%" then 'cc' 
when page like "%dd%" then 'dd' 
else 'Fail' end as category from "5_tokushu"