コホート分析を繰り返します。統合されたアプローチ-Python、SQL、Power BI

こんにちは、読者の皆様！この記事は、Python Revisiting Power BIコホート分析（リンク）のフォローアップです。少なくとも簡単に知っておくことを強くお勧めします。そうしないと、その後の話が理解できなくなります。 Habrでリリースされてから十分な時間が経過しました。そのような問題を解決するための方法論を徹底的に改訂しました。私の最初の望みは単に古い資料を書き直すことでしたが、いくつかの審議の結果、新しい原稿の開発を形式化することがより合理的なステップであるという結論に達しました。

PythonとPowerBIに対する私の「不満」の根本的な原因は何ですか？テーマ別ライブラリとPowerBI（Tableau、Qlik）を備えたPython / R言語は、複雑なメトリックの計算と視覚化の構築におけるビジネスニーズの70〜80％をカバーできます。ただし、すでに集計されたデータを含む比較的小さなデータセットを処理する場合に限ります。産業規模での予備的なデータ操作について話している場合、ここでゲームはデータベースを使用してサーバー側に切り替わり、SQLを使用します。前回の出版物ではこの点を取り上げていなかったので、ここでこの省略をなくすことにしました。

SQLクエリの開発とテストには、PostgreSQLデータベースを選択しました。このデータベースをラップトップにローカルにインストールしました。特定の設定はしていませんが、すべてのパラメータはそのままにしておきました。資料で説明されている手順を繰り返すには、Dockerに精通している場合は、PostgreSQLでコンテナを起動することも適しています。

データセットはcsv形式で、ファイルはスクリプト付きでGitHub（リンク）にあります。情報は事前に直接ロードするために準備されていたので、組み込みのpgAdminプログラムを使用するだけで済みました。グラフィックエディタモードでの読み込み時間は100万行強で、4〜5秒です。このメトリックは、Pythonコードで打ち負かすことができなかったため、ベンチマークになりました。デモ例のニーズに合わせてスクリプトを使用してPostgreSQLにデータをロードすることは実装されていない可能性がありますが、分析の簡単な方法を探していません。

最初のステップは、salesテーブルを作成することです。コード自体は非常に単純で、追加のコメントは必要ありません。

import psycopg2

#   
conn = psycopg2.connect("dbname='db' user='postgres' password='gfhjkm' host='localhost' port='5432'")

print("Database opened successfully")

#  
cursor = conn.cursor()

with conn:
    cursor.execute("""
            DROP TABLE IF EXISTS sales;
        """)

    cursor.execute("""
            CREATE TABLE IF NOT EXISTS sales (
              id SERIAL PRIMARY KEY,
              date DATE NOT NULL, 
              promo TEXT NOT NULL,
              site TEXT NOT NULL,
              user_id TEXT NOT NULL,
              transaction_id INTEGER NOT NULL,
              amount INTEGER NOT NULL);
        """)


print("Operation done successfully")

#    
cursor.close()
conn.close()
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

テーブルが作成されたら、次のスクリプトを実行してデータベースにデータを書き込みます。パンダとsqlalchemyは連携して機能します。並行して、datetimeを使用して時間を測定します。

import os
import pandas as pd
import psycopg2
from sqlalchemy import create_engine
from datetime import datetime

start_time = datetime.now()

#   
engine = create_engine('postgresql://postgres:gfhjkm@localhost:5432/db')

print("Database opened successfully")

#     
path_to_data = "C:/Users/Pavel/PycharmProjects/database/"
#    
sale_records = pd.read_csv(os.path.join(path_to_data, "ohortAnalysis_2016_2018.csv"),
                           sep=";", parse_dates=["date"], dayfirst=True)
postgresql_table = "sales"
#    
sale_records.to_sql(postgresql_table, engine, if_exists='append', index=False)

print("Operation done successfully")

end_time = datetime.now()
print('Duration: {}'.format(end_time - start_time))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

3 26 . . , sqlalchemy .

import psycopg2
from datetime import datetime

start_time = datetime.now()

#   
conn = psycopg2.connect("dbname='db' user='postgres' password='gfhjkm' host='localhost' port='5432'")
print("Database opened successfully")

#  
cursor = conn.cursor()

#     
path_to_data = "C:/Users/Pavel/PycharmProjects/database/"
#    
sale_records = pd.read_csv(os.path.join(path_to_data, "ohortAnalysis_2016_2018.csv"),
                           sep=";", parse_dates=["date"], dayfirst=True)

query = "INSERT INTO sales (date, promo, site, user_id, transaction_id, amount) values (%s, %s, %s, %s, %s, %s)"
dataset_for_db = sale_records.values.tolist()

cursor.executemany(query, dataset_for_db)
conn.commit()

print("Operation done successfully")

#    
cursor.close()
conn.close()

end_time = datetime.now()
print('Duration: {}'.format(end_time - start_time))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

10 . – pandas.

import psycopg2
from datetime import datetime

start_time = datetime.now()

#   
conn = psycopg2.connect("dbname='db' user='postgres' password='gfhjkm' host='localhost' port='5432'")
print("Database opened successfully")

#  
cursor = conn.cursor()


#  .       
with open('ohortAnalysis_2016_2018.csv', 'r', encoding='UTF8') as f:
    next(f)
    cursor.copy_from(f, 'sales', sep=';', columns=('date','promo','site','user_id','transaction_id','amount'))
    conn.commit()

f.close()

print("Operation done successfully")

#    
cursor.close()
conn.close()

end_time = datetime.now()
print('Duration: {}'.format(end_time - start_time))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

7 . . . . , pandas .

SQL , . . Python Power BI , . SQL .

SELECT s3.date,
	s3.user_id,
	s3.date - s2.first_date AS delta_days,
	ceil((s3.date - s2.first_date)::real/30::real)*30 AS cohort_days,
	to_char(s2.first_date,'YYYY-MM') AS first_transaction
	s3.amount
FROM public.sales AS s3
LEFT JOIN
				(SELECT s1.user_id,
						MIN(s1.date) AS first_date
					FROM public.sales AS s1
					GROUP BY s1.user_id) AS s2 ON s3.user_id = s2.user_id
ORDER BY s3.user_id,
	s3.date


SELECT  s.date,
		s.user_id,
		s.date - FIRST_VALUE(s.date) OVER(PARTITION BY s.user_id ORDER BY s.date) AS delta_days,
		ceil((s.date - FIRST_VALUE(s.date) OVER(PARTITION BY s.user_id ORDER BY s.date))::real/30::real)*30 AS cohort_days,
		to_char(FIRST_VALUE(s.date) OVER(PARTITION BY s.user_id ORDER BY s.date),'YYYY-MM') AS first_transaction,
		s.amount
FROM public.sales AS s
ORDER BY s.user_id,
	s.date
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , , . . PostgreSQL to_char().

( ) . - , CASE. , 3 . . , , . PostgreSQL . - .

. – 30 . 30. 0 30, 0, . 0 30 30. , . , 30 30, 1, . , , . PostgreSQL ceil(). 30 .

. INTEGER INTEGER, . ! , ::real .

: SQL .

, .

, .

SELECT r2.first_transaction,
		r2.cohort_days,
		--r2.total_amount,
		--sum(r2.total_amount) OVER (PARTITION BY r2.first_transaction ORDER BY r2.first_transaction, r2.cohort_days) as cumsum_amount,
		--first_value(r2.total_amount) OVER (PARTITION BY r2.first_transaction ORDER BY r2.first_transaction, r2.cohort_days) as first_total_amount,
		round((sum(r2.total_amount) OVER (PARTITION BY r2.first_transaction ORDER BY r2.first_transaction, r2.cohort_days)/ 
		first_value(r2.total_amount) OVER (PARTITION BY r2.first_transaction ORDER BY r2.first_transaction, r2.cohort_days)-1),3) as percent_cumsum_amount
FROM 
		(SELECT r.first_transaction, r.cohort_days, sum(r.amount) AS total_amount		
		FROM public.report_cohort_analysis AS r
		GROUP BY r.first_transaction, r.cohort_days
		ORDER BY r.first_transaction, r.cohort_days) as r2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . , . (- ). - . ().

, .

. – SQL. PostgreSQL CROSSTAB, . BI . Power BI , ( , Python). ( SQL). .

この出版物を次のように締めくくりたいと思います。最高の分析ソリューションは、1つのツールからすべてのジュースを絞り出すのではなく、さまざまなプラットフォームの機能の最適な組み合わせを中心に構築されています。

それで全部です。すべての健康、幸運、そしてプロとしての成功！