February 2, 2021 01:00 AM
BigQuery
■Big queryリファレンス標準SQLとレガシーSQLがある、違いは？標準 SQL のクエリ構文 | BigQuery | Google Cloud標準 SQL への移行 | BigQuery | Google Cloud標準 SQL のデータ型 | BigQuery | Google Cloudレガシー SQL 関数と演算子 | BigQuery | Google Cloudレガシー SQL のデータ型 | BigQuery | Google CloudBigQuery: クラウド データ ウェアハウス | Google C…
///BigQuery
■nullが最小か調べる(BigQueryではnullが最小値)
select * from bangboo_data.x_data order by value
■NULLの件数を事前に数えておく(nullを0に変換してカウントした)
SELECT *
FROM (SELECT 'value', count(COALESCE(value,0)) FROM bangboo_data.x_data WHERE value IS NULL)
union all
(SELECT 'value2', count(COALESCE(value2,0)) FROM bangboo_data.x_data WHERE value2 IS NULL)
union all
(SELECT 'v_no_null', count(COALESCE(v_no_null,0)) FROM bangboo_data.x_data WHERE v_no_null IS NULL)
->4件、４件、0件で正常なのでcount(COALESCE(value,0))と条件はis nullを使う
横並びなら、単一テーブルなのでFromはCross joinでOK、SQLは0件のカウントをGroup byできないので↑が安全かも
SELECT count_null_1, count_null_2, count_null_3
FROM (SELECT count(COALESCE(value,0)) as count_null_1 FROM bangboo_data.x_data WHERE value IS NULL) as t1,
(SELECT count(COALESCE(value2,0)) as count_null_2 FROM bangboo_data.x_data WHERE value2 IS NULL) as t2,
(SELECT count(COALESCE(v_no_null,0)) as count_null_3 FROM bangboo_data.x_data WHERE v_no_null IS NULL) as t3
SELECT v_no_null, count(COALESCE(v_no_null,0)) as count_null_3 FROM bangboo_data.x_data WHERE v_no_null IS NULL GROUP BY v_no_null
https://www.ksakae1216.com/entry/2017/02/17/063000
■nullと0と''の違い
select * from bangboo_data.x_data where v_no_null = 0
->4行でる
select * from bangboo_data.x_data where v_no_null is null
->0行
==>0はnullでなくwhere a = 0で検索せよ
select * from bangboo_data.x_purchase where item = ''
->2行でる
select * from bangboo_data.x_purchase where item is null
->0行
==>
''はnullでなくwhere a = ''で検索せよ■全削除
delete from bangboo_data.x_employee where true
truncate table bangboo_data.x_employee
■テーブル削除
drop table bangboo_data.x_xxx
■DDLとデータ入力
CREATE TABLE IF NOT EXISTS `bangboo_data.x_data` (
`no` INT64 NOT NULL,
`value` INT64,
`value2` INT64,
`v_no_null` INT64 NOT NULL,
)
INSERT INTO `bangboo_data.x_data` (`no`, `value`, `value2`, `v_no_null`) VALUES
(1, 100, NULL, 0),
(2, NULL, 200, 0),
(3, NULL, 200, 5),
(4, NULL, 100, 10),
(5, NULL, 300, 0),
(6, 200, NULL, 0),
(7, 200, NULL, 5),
(8, 100, NULL, 10);
CREATE TABLE IF NOT EXISTS bangboo_data.x_bill (
`no` INT64 NOT NULL,
`user_no` INT64 NOT NULL,
`total_bill` INT64 NOT NULL,
`date` DATETIME,
)
INSERT INTO `bangboo_data.x_bill` (`no`, `user_no`, `total_bill`, `date`) VALUES
(1, 1, 1000, '2021-01-01 00:00:00'),
(2, 1, 1500, '2021-01-02 00:00:00'),
(3, 1, 500, '2021-01-03 00:00:00'),
(4, 2, 1100, '2021-01-01 00:00:00'),
(5, 2, 1700, '2021-01-02 00:00:00'),
(6, 2, 800, '2021-01-03 00:00:00'),
(7, 3, 1100, '2021-01-01 00:00:00'),
(8, 3, 1700, '2021-01-02 00:00:00'),
(9, 3, 800, '2021-01-03 00:00:00'),
(10, 3, 800, '2021-01-04 00:00:00');
CREATE TABLE IF NOT EXISTS `bangboo_data.x_compensation` (
`no` INT64 NOT NULL,
`allow` STRING
)
INSERT INTO `bangboo_data.x_compensation` (`no`, `allow`) VALUES
(1, '住宅'),
(1, '交通'),
(2, '住宅');
CREATE TABLE IF NOT EXISTS `bangboo_data.x_employee` (
`no` INT64 NOT NULL,
`name` STRING
)
INSERT INTO `bangboo_data.x_employee` (`no`, `name`) VALUES
(1, '田中'),
(2, '鈴木'),
(3, '佐藤');
CREATE TABLE IF NOT EXISTS `bangboo_data.x_joblevel` (
`no` INT64 NOT NULL,
`res` STRING
)
INSERT INTO `bangboo_data.x_joblevel` (`no`, `res`) VALUES
(1, '部長'),
(1, '課長'),
(2, '平');
CREATE TABLE IF NOT EXISTS `bangboo_data.x_purchase` (
`no` INT64 NOT NULL,
`p_no` INT64 NOT NULL,
`item` STRING,
`price` INT64 NOT NULL,
`unit` INT64 NOT NULL,
)
INSERT INTO `bangboo_data.x_purchase` (`no`, `p_no`, `item`, `price`, `unit`) VALUES
(1, 1, 'aaa', 100, 10),
(2, 2, 'aaa', 100, 10),
(3, 2, '', 100, 5),
(4, 3, 'bbb', 100, 5),
(5, 4, 'ccc', 300, 3),
(6, 4, 'aaa', 100, 2),
(7, 5, 'ccc', 300, 5),
(8, 5, 'bbb', 100, 2),
(9, 6, 'aaa', 100, 3),
(10, 6, 'bbb', 100, 2),
(11, 6, 'ccc', 300, 1),
(12, 7, 'aaa', 100, 11),
(13, 8, 'bbb', 100, 17),
(14, 9, 'ccc', 300, 2),
(15, 9, 'aaa', 100, 2),
(16, 10, 'ccc', 300, 2),
(17, 10, '', 100, 2);
■view
CREATE VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;
ちなみにdeclareが入っているSQLはviewにできない
■簡易RFM(recency frequenctry monetary)
SELECT name, MAX(date), count(DISTINCT x_bill.no), SUM(price * unit) FROM bangboo_data.x_employee
RIGHT JOIN bangboo_data.x_bill ON x_employee.no = x_bill.user_no
RIGHT JOIN bangboo_data.x_purchase ON x_bill.no = x_purchase.p_no
GROUP BY name ORDER BY SUM(price * unit) DESC
■RFM
SELECT RANK() OVER (ORDER BY rank_recent+rank_frequent+rank_money) as rank, rank_recent+rank_frequent+rank_money as least_score_wins, t1_r.no as user_no, t1_r.name, recent, frequent, money, rank_recent, rank_frequent, rank_money
FROM (
SELECT RANK() OVER (ORDER BY recent DESC) as rank_recent, t1.no, t1.name, recent
FROM (SELECT x_employee.no, x_employee.name, MAX(date) as recent FROM bangboo_data.x_employee RIGHT JOIN bangboo_data.x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN bangboo_data.x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_employee.no, x_employee.name ORDER BY MAX(date) DESC) as t1
) as t1_r
LEFT OUTER JOIN (
SELECT RANK() OVER (ORDER BY frequent DESC) as rank_frequent, t2.user_no, t2.name, frequent
FROM (SELECT x_bill.user_no, x_employee.name, count(distinct x_bill.no) as frequent FROM bangboo_data.x_employee RIGHT JOIN bangboo_data.x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN bangboo_data.x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_bill.user_no, x_employee.name ORDER BY count(distinct x_bill.no) DESC) as t2
) as t2_f
ON t1_r.no = t2_f.user_no
LEFT OUTER JOIN (
SELECT RANK() OVER (ORDER BY money DESC) as rank_money, t3.no, t3.name, money
FROM (SELECT x_employee.no, x_employee.name, SUM(price * unit) as money FROM bangboo_data.x_employee RIGHT JOIN bangboo_data.x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN bangboo_data.x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_employee.no, x_employee.name ORDER BY SUM(price * unit) DESC) as t3
) as t3_m
ON t2_f.user_no = t3_m.no
コメント (0)
February 2, 2021 12:00 AM
Python Python
■importとfromPythonのモジュールとimportとfrom入門 - QiitaPython, importの使い方（from, as, PEP8の推奨スタイル, 注意点など） | note.nkmk.mePythonインポート周り徹底理解への道 - QiitaPythonの相対インポートで上位ディレクトリ・サブディレクトリを指定 | note.nkmk.me標準ライブラリならimport文を本体に書いていれば良い 標準ライブラリ以外はPyPI（Python Package Index）と呼ばれる3rdパーティライブラリから pip（The Python Package Installer）インスコimport文を使って下記の3つなどをインポートし使う 標準ライブラリ pipでインスコしたパッケージ 自作のパッケージ自作は大体わかるがそれ以外はどこにあるのか？ $ python…
コメント (0)
February 2, 2021 07:30 PM
Python
おッPythonやるのか？ファイル拡張子oppython.py デフォUTF-8、全部オブジェクト(list,dict,set等のミュータブルなら参照になる点に注意、必要ならcopy())#コメント、ドキュメントストリング(三連引用符)："""そのまま表示""" print mymod.__doc__で見れる変数型不要：p = 500 * num、でもキャストは必要、定数はない文字繰り返し、キャスト："文字列" * 4 + str(p) + "Hi\nお元気ですか？\nSee u"raw文字列でescしない：print(r"インストール先は c:\\code\python\bin です") 正規表現のrも同意 re_result = re.match('hel', r'hellow python, 123, end.' ) if re_result: #None以外という意味で、None…
def oex():
#globalつけるかつけないかでローカル変数かを決める
global vibe
rotion = 'g'
vibe = 'Pink'
rotion = 'a'
vibe = 'Gray'
print(vibe)
oex()
print(vibe)
print(rotion)
#defは先んじて宣言が必要
###################################
x = 'Aho'
def sexWax(a = x):
print(a)
sexWax()
sexWax('Baka')
#関数の初期値は先んじて宣言が必要
###################################
import random
class Unco:
init_size = 1
init_color = '茶'
def __init__(self, size = init_size, color = init_color):
self.size = size
self.color = color
def wayBy(self):
return ['Ketsu','Shiri','Oido']
def getUnco(self, how):
how = how[random.randrange(0, 3, 1)]
return 'Size' + str(self.size) + 'で色が' + str(self.color) + 'の奴を手法は' + how
#クラスの中はselfを付ける
kuso = Unco()
how = kuso.wayBy()
if len(how) < 1:
how = 'Anal'
way = kuso.getUnco(how)
print(way)
import random
class Ctl:
def __init__(self):
self.flag_ok = 1
def run(self):
for num in range(random.randrange(0, 3, 1)):
print(num)
if num == 1:
self.flag_ok = 0
print('マッチしました')
break
exe = Ctl()
exe.run()
while True:
if exe.flag_ok == 1:
exe.run()
else:
print('終了します')
break
コメント (0)
ナビ:
< 18 | 19 | 20 | 21 | 22 >
(C)Blog.Bangboo.COM