March 3, 2021 01:43 PM
ZERO
値段と糖質とプリン体と度数、モノによっては明らかに太る感じがしない、ゆって0じゃ無い安酒でも糖質15g位らしいので良いのがなければいつものでいいかも。それより現像ができない!!→LightRoom再インスコ→LRよりSony謹製の方が色が良かった→元画像が暗すぎが理由→LRでノイズを追加調整(元画像に注意、14mmf1.8の使い道がなく勿体ない、マクロとして物撮りしたい感じがあるがマクロは70-200の方がええし、14は人物用だな)極ZERO: 138/0/0/5 ちょい高、不味い、5%で軽いが酔えなくはない、太る感じ×Asahi Off: 118/0/0/3-4 酔えない、ノンアルの亜種かフルーティで旨い〇のどごしZERO: 118/0/0/4 フルーティで旨い、軽いが酔えなくはない〇贅沢ZERO: 118/0/x/6 プリン有 6%で嬉しいが雑味がすごくある気が▲バーリアル糖質50%o…
コメント (0)
February 2, 2021 12:00 AM
BigQuery part2
■BQタイムアウトは6時間■BQ transfer(クエリが不要なBQ連携、3rd partyもあり)サードパーティ転送を使用する | BigQuery | Google CloudCloud Storage の転送の概要 | BigQuery | Google CloudAmazon S3 転送の概要 | BigQuery | Google Cloudデータセットコピー、GCSファイルAma S3, Azure storage, Oracle, Salesforce, Ads系等々■Cloud SQLにBQからクエリSELECT * FROM EXTERNAL_QUERY("connection_name", "SELECT * FROM db.…
コメント (0)
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)
ナビ:
< 18 | 19 | 20 | 21 | 22 >
(C)Blog.Bangboo.COM