Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。

BigQueryのCROSS JOINとwindow関数で移動統計量算出【Sexy Tech for You #3】

f:id:abe_masatoshi:20210701225140p:plain

データサイエンティストの阿部です。東京五輪開会式の選手入場を何度か見返してしまいました。冒頭とカザフスタン入場のシーンは胸が高鳴りました。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第3話を配信しました。動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube


BigQuery クエリ

user_idとdateの例

#StandardSQL 
--user_idとdateでユニークなtableがあるとする。またdateは集計したい期間に関してtable内に1レコード以上あるとする

WITH

user_master AS (SELECT DISTINCT user_id FROM table),

date_master AS (SELECT DISTINCT date FROM table)

SELECT
    *
    ,AVG(views)OVER(PARTITION BY user_id ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS avg_views_3b_1b
FROM(
SELECT 
    A.user_id
    ,B.date
    ,IFNULL(C.views, 0) AS views
FROM
    user_master A CROSS JOIN 
    date_master B LEFT JOIN 
    dat C ON A.user_id = C.user_id AND B.date = C.date
)

BigQueryの一般公開データセットbigquery-public-data.wikipedia.pageviews_2021での適用例

#StandardSQL

WITH

-- 一般公開データセットを利用
dat AS (
SELECT 
    DATE(datehour) AS date
    ,title -- ユーザーに該当するカラムとしてtitleを使用
    ,SUM(views) AS views
FROM
    `bigquery-public-data.wikipedia.pageviews_2021`
WHERE
    DATE(datehour) BETWEEN DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 7 DAY) AND DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)
    AND wiki = 'ja'
GROUP BY 
    1,2
),

title_master AS (
SELECT 
    DISTINCT title
FROM
    dat
),

date_master AS (
SELECT 
    DISTINCT date
FROM
    dat
)

SELECT
    *
    ,AVG(views)OVER(PARTITION BY title ORDER BY date ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS avg_views_3b_1b
FROM(
SELECT 
    A.title
    ,B.date
    ,IFNULL(C.views, 0) AS views
FROM
    title_master A CROSS JOIN 
    date_master B LEFT JOIN 
    dat C ON A.title = C.title AND B.date = C.date
)
ORDER BY 
    title, date

補足

BigQueryのwindow関数は、対象レコードが1レコードでも計算対象が存在すれば計算するため、例えば直前3日間の移動平均を計算する場合に2日目以降から値が算出されます。期間が満了しているレコードにのみ値を付与したい場合は、例えばCOUNT()OVER(PARTITION BY)で行数をカウントして条件指定するなど、別途制御することが必要です。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした!次回またお会いしましょう。

BigQueryで確率乱数を生成【Sexy Tech for You #2】

f:id:abe_masatoshi:20210701225140p:plain

データサイエンティストの阿部です。最近来てくれたインターン生と10歳違いなことに驚きました。光陰矢の如し!

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第2話を配信しました。動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube


BigQuery クエリ

正規乱数

#StandardSQL
-- ボックス=ミュラー法に基づく正規乱数発生関数
CREATE TEMPORARY FUNCTION RNORM(mu FLOAT64, sigma FLOAT64) AS (
  (SQRT(-2*LOG(RAND()))*SIN(2*ACOS(-1)*RAND())) * sigma + mu  
);

-- 10万件サンプリング
SELECT
    sample_id,
    RNORM(171.2, 5.5) AS sample_hight  -- 関数の入力値は平均と標準偏差
FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) AS sample_id

指数乱数

#StandardSQL
-- 逆関数法による指数乱数発生関数
CREATE TEMPORARY FUNCTION REXP(lambda float64) AS (
    -(1/lambda) * LOG(1-RAND()) 
);

-- 10万件サンプリング
SELECT
    sample_id,
    REXP(0.01) AS sample_interval
FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) AS sample_id

ポアソン乱数

#StandardSQL

-- 指数乱数発生関数
CREATE TEMPORARY FUNCTION REXP(lambda float64) AS (
    -(1/lambda) * LOG(1-RAND()) 
);

-- ポアソン分布のパラメータ ラムダを指定
CREATE TEMPORARY FUNCTION lambda() AS (
    0.05
);

-- B*Cコの指数乱数列を発生させて、累積和が1以上に到達する乱数の個数を取得する試行をA回繰り返して、ポアソン乱数のテーブルを作成する
CREATE TEMP TABLE rpois_set AS (
SELECT 
    *,
    ROW_NUMBER()OVER() AS rownum
FROM(
SELECT
    session,
    COUNT(1) - 1 AS rpois_val
FROM(
SELECT 
    *
FROM(
SELECT 
    *,
    SUM(start_flg)OVER(ORDER BY trial ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
FROM(
SELECT 
    ROW_NUMBER()OVER() AS trial,
    REXP(lambda()) AS rexp,
    IF(B=1 AND C=1, 1, 0) AS start_flg
FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) AS A, -- セッション数。現在のBigQueryの生成数限界は POWER(2,20)-1
    UNNEST(GENERATE_ARRAY(1, CEIL(lambda()))) AS B, -- B*Cが試行回数。ポアソン分布のパラメータ ラムダの10倍としている
    UNNEST(GENERATE_ARRAY(1, 10)) AS C
))
WHERE 
    trial IS NOT NULL -- QUALIFY句利用の際にWHERE指定が必要なため。特に意味はない
    QUALIFY IFNULL(SUM(rexp)OVER(PARTITION BY session ORDER BY trial ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) < 1 
)
GROUP BY
    1
HAVING 
    SUM(rexp) >= 1
));

-- ポアソン乱数テーブルから乱数を取得する関数を定義
CREATE TEMP FUNCTION RPOIS(target_rownum int64)AS ((
    SELECT rpois_val
    FROM rpois_set
    WHERE rownum = target_rownum
));

-- テーブル行数以上のサンプリングをする場合に備えて行数取得
CREATE TEMPORARY FUNCTION limit_sample_num() AS ((
    SELECT MAX(rownum) FROM rpois_set
));


--100万件サンプリング
SELECT 
    sample_id,
    RPOIS(target_rownum) AS sample_action_cnt
FROM(
SELECT
    sample_id,
    IF(MOD(sample_id,limit_sample_num())!=0, MOD(sample_id,limit_sample_num()), limit_sample_num()) AS target_rownum --乱数テーブルの行数以上のサンプル数が必要な場合に備えて、テーブル行数で剰余を取る
FROM
    UNNEST(GENERATE_ARRAY(1, 1000000)) AS sample_id
)

補足

ポアソン乱数発生関数は他の2つの関数と異なり、一時的にポアソン乱数テーブルを作成し、そのテーブルから指定のサンプル数だけポアソン乱数を取得するというフローになっています。確率分布のパラメータも関数内ではなく、クエリの冒頭で定義しています。また乱数テーブルを作成するための計算過程で、ポアソン分布のパラメータの整数倍でレコード数が増えるため、ラムダが30を超えるとメモリ不足エラーで計算不能となりましたので、ご留意ください。

動画でも述べておりますが、ポアソン乱数発生関数は、JavaScriptを用いてUDFを定義した方がシンプルで便利だと思いますが、今回はSQLで統一することを優先しましたのでご容赦頂けましたら幸いです。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした!次回またお会いしましょう。

Googleスプレッドシートの表をBigQueryに連携して縦持ちテーブルに動的変換【Sexy Tech for You #1】

f:id:abe_masatoshi:20210701225140p:plain

データサイエンティストの阿部です。今後不定期に、データ分析のためのデータハンドリングTipsを、Youtubeで発信します。各回3分でBigQueryに関する話題が中心です。"Sexy Tech for You" と題して、思わず使いたくなるような魅力的なTipsを紹介していきたいと思います。

動画中に記載したコードや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。では早速、第1回目の配信についてです。

Youtube


BigQuery クエリ

#StandardSQL

DECLARE id,target,not_target STRING;

# INFORMATION_SCHEMA.COLUMNSからフィールド名取得
SET (target, not_target) = (
SELECT AS STRUCT
  CONCAT('(', STRING_AGG(IF(STARTS_WITH(column_name,'string_field_')=TRUE , column_name, NULL), ','), ')'),
  IFNULL(CONCAT('EXCEPT (', STRING_AGG(IF(STARTS_WITH(column_name,'string_field_')=FALSE, column_name, NULL), ','), ')'), ' ')  #string_field_*以外のフィールドがあった場合にUNPIVOT対象から除外するためのフィールドセット。NULLのとき半角スペースにしておかないと後続でエラーになる
FROM
  `project_id.dataset_name`.INFORMATION_SCHEMA.COLUMNS #データセット名(青)
WHERE
  table_name = 'table_name' #テーブル名のみ(青)
  AND column_name != 'string_field_1' #キーにしたいフィールド名(赤)
);

# 動的なフィールド名SETを読み込むため、EXECUTE IMMEDIATE FORMATでクエリ発行
EXECUTE IMMEDIATE FORMAT("""
WITH base AS (
SELECT
  *,
  string_field_1 AS value_type
FROM
  (SELECT * EXCEPT %s FROM `project_id.dataset_name.table_name` #キーにしたいフィールド名(赤)
  UNPIVOT(value FOR key IN %s)
)

SELECT
  B.value AS id,
  A.value_type,
  A.value
FROM
  base A INNER JOIN
  (SELECT * FROM base WHERE value_type = '月') B ON A.key = B.key #キーにしたいスプレッドシート入力値(黄)
WHERE
  A.value_type != '月' #キーにしたいスプレッドシート入力値(黄)
""", not_target, target);

補足

結果は以下のように出てきます。3つ目のステージの[結果を表示]から、最終的な出力結果を確認できます

f:id:abe_masatoshi:20210531191659p:plain
出力結果例

f:id:abe_masatoshi:20210531191910p:plain
出力テーブル例

string_field_*target というフィールド名のセットとして使い、それ以外のフィールドをnot_target として除いてUNPIVOT しています。別の型のフィールドを扱う必要がある場合は、この辺りを変更すれば対応できるかと思います。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした!次回またお会いしましょう。

コンテンツデータをEC2からAWS Fargate+Amazon EFSに移行した話

f:id:modokkin:20210422102643p:plain

どうも、ヤプリ SREチームのはぶちんです。久しぶりのブログ投稿です。
今回はEC2上に保管されたコンテンツデータをAWS Fargate(以下Fargate)+Amazon EFS(以下EFS)に移行したので、困ったことや結果的にどう実現したかをご紹介したいと思います。

ヤプリって?

Yappli(ヤプリ)はアプリ開発・運用・分析をノーコード(プログラミング不要)で提供するアプリプラットフォームです。 詳細はサービスサイトをご覧ください。 https://yapp.li/

ヤプリのコンテンツデータって?

本記事では、アプリのコンテンツを表現するために必要な設計図のようなものをコンテンツデータとして表現しています。
このデータは、可能ならデータ自体をRDBなどスケール可能な仕組みに移行する事が好ましいのですが、ヤプリで提供している機能が多岐にわたり移行にはまだしばらく時間を要するため、現在でも一部でファイルを扱う仕組みを採用しています。

続きを読む

「デザインシステムの導入と運用」についてDesignship 2020で登壇してきました

f:id:jo_yapp:20201120153909p:plain

こんにちは。UI/UXデザイナーの城です。

前回の記事では、山﨑による「Design Ship 2020でスポンサー登壇しました - Yappli Tech Blog」をお伝えしました。今回はその翌々週に行われたDesignship 2020 Extra Stageのパネルディスカッションで城が登壇した報告をさせていただきます。  

続きを読む

Design Ship 2020で『プラットフォームのUI/UXデザインで世界を変える』について登壇しました

f:id:yamazaki_risa:20201125092854p:plain
こんにちは!
最近個人的趣味でiPadお絵かきにハマっている
UI/UXデザイナーの山﨑です。

さて、先日Design Ship 2020にて、スポンサー枠で登壇しました。

Design Shipって?

design-ship.jp
デジタル・グラフィック・プロダクトにおける「デザイン」の知識や経験を、
それぞれの業界のデザイナーたちが壁を越えて、
学び、鼓舞し合うような機会を提供することで、
国⺠の創造性向上に寄与することを目的としたカンファレンスです。

お話したこと

ヤプリからは
『プラットフォームのUI/UXデザインで世界を変える』
というテーマでお話させていただきました。


ここでは、プラットフォームのUI/UX設計で重要なポイントと
そこから私たちがどういった設計をしているのかという内容を中心に、
私たち、ヤプリのUI/UXデザイナーが目指すUI/UX設計のお話をさせていただきました。


お話した内容はこちらの資料にまとめていますので、ぜひご覧ください。
speakerdeck.com

さいごに

今年のDesign Shipのイベントは、コロナの影響で初のオンライン生配信という形で行われました。
バリバリのスタジオからお送りした形となり、雰囲気にとても緊張したのですが、
人生で初めてこういった大きなイベントで登壇させていただき、観てくださったみなさまに、
少しでもヤプリのUI/UXデザイナーへの興味や関心をもっていただけたことが嬉しく思っています。

また後日、この登壇のお話の続きとして「Yappli ねほりはほり for UI/UXデザイナー」という
自社イベントを開催させていただいたのですが、こちらのお話はまた別途記事にさせていただきます!

iOSDC Japan 2020 に参加しました✨

  • 参加メンバー(寄稿者) 👤
  • はじめに
  • イベントの様子
    • オープニング
    • トークセッション
    • LT
    • クロージング
  • 印象に残ったセッション
    • SourceKit LSPをブラウザでコードを読むために活用する
    • オープンソースのAltSwiftUIの発表
    • キーワード多すぎ!なエンタープライズiOSの世界を概観する - 2020年版
    • そろそろCombine
    • iOS のキーボードと文字入力のすべて
    • iOSアプリ開発のための"The Composable Architecture"がすごく良いので紹介したい
    • 今日から分かるAVAudioEngineの全て
    • 100人でアプリをリファクタリングして見えてきた、最強のiOSアプリ設計に求められること
    • スモールビジネスを支えるfreeeのモバイル技術
    • GitHub ActionsでiOSアプリをCIする個人的ベストプラクティス
    • Flutter移行の苦労と、乗り越えた先に得られたもの
    • google/mediapipe で始めるARアプリ開発
  • まとめ
  • PR

参加メンバー(寄稿者) 👤

古賀山本みしま

はじめに

ヤプリiOSエンジニアの古賀です。

今年もやってきました iOSDC Japan 2020 !
iOSD JapanはiOS関連技術をコアのテーマとした技術者のためのカンファレンスです。

弊社は「iOSDC JAPAN 2020」にスポンサーしています!

今回はカンファレンスの様子や、参加したアプリエンジニアの感想や印象に残ったことについて記事を書きました。

note.com

続きを読む

Copyright © 2018 Yappli, Inc. All rights reserved