Guten Tag, liebe Leser! Dieser Artikel ist eine Fortsetzung von Python Revisiting Power BI Cohort Analysis ( Link ). Ich empfehle Ihnen dringend, es zumindest kurz kennenzulernen, da sonst die nachfolgende Geschichte für Sie unverständlich wird. Seit seiner Veröffentlichung auf Habr ist genug Zeit vergangen. Ich habe die Methodik zur Lösung solcher Probleme gründlich überarbeitet. Mein erster Wunsch war es einfach, das alte Material neu zu schreiben, aber nach einiger Überlegung kam ich zu dem Schluss, dass es ein vernünftigerer Schritt wäre, die Entwicklungen in einem neuen Manuskript zu formalisieren.
Was ist die Hauptursache für meine "Unzufriedenheit" mit Python und Power BI? Die Python / R-Sprache mit thematischen Bibliotheken und Power BI (Tableau, Qlik) kann 70-80% der Geschäftsanforderungen bei der Berechnung komplexer Metriken und der Erstellung von Visualisierungen abdecken. Aber nur, wenn es darum geht, relativ kleine Datensätze mit bereits aggregierten Daten zu verarbeiten. Wenn es sich um eine vorläufige Datenmanipulation im industriellen Maßstab handelt, wechselt das Spiel hier mit der Datenbank auf die Serverseite und verwendet SQL. Ich habe diesen Punkt in der vorherigen Veröffentlichung nicht behandelt, daher habe ich beschlossen, diese Auslassung hier zu beseitigen.
Zum Entwickeln und Testen von SQL-Abfragen habe ich mich für die PostgreSQL-Datenbank entschieden. Ich habe diese Datenbank lokal auf einem Laptop installiert. Ich habe keine spezifischen Einstellungen vorgenommen, sondern alle Parameter unverändert gelassen. Um die im Material beschriebenen Schritte zu wiederholen, eignet sich das Starten eines Containers mit PostgreSQL auch, wenn Sie mit Docker befreundet sind.
Sie finden Dataset im CSV-Format und Dateien mit Skripten auf GitHub ( Link ). Da die Informationen im Voraus für das direkte Laden vorbereitet wurden, musste ich nur das integrierte Programm pgAdmin verwenden. Die Ladezeit beträgt im Grafikeditor-Modus etwas mehr als 1 Million Zeilen, 4-5 Sekunden. Diese Metrik ist zu einem Benchmark geworden, weil ich sie mit Python-Code nicht schlagen konnte. Das Laden von Daten in PostgreSQL mithilfe von Skripten für die Anforderungen des Demo-Beispiels wurde möglicherweise nicht implementiert, wir suchen jedoch nicht nach einfachen Möglichkeiten für die Analyse.
Der erste Schritt besteht darin, die Verkaufstabelle zu erstellen. Der Code selbst ist extrem einfach und erfordert keine zusätzlichen Kommentare.
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()
Die Tabelle wird gebildet, wir führen das folgende Skript aus, um Daten in die Datenbank zu schreiben. Pandas und Sqlalchemie arbeiten zusammen. Parallel dazu messen wir die Zeit mit 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). .
Ich möchte diese Veröffentlichung mit dem folgenden Gedanken abschließen. Die besten Analyselösungen basieren auf der optimalen Kombination der Funktionen verschiedener Plattformen, anstatt alle Säfte aus einem Werkzeug herauszupressen.
Das ist alles. Alle Gesundheit, viel Glück und beruflichen Erfolg!