BI умер, да здравствует BI

Измененный python-скрипт from sqlalchemy import create_engine import pandas as pd import streamlit as st # Создаем движок подключения engine = create_engine('postgresql+psycopg2://postgres:admin@localhost:5432/databaase') # получаем и кэшируем данные spinner_text = "Чтение данных..." @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_unique_values(column): """Получаем уникальные значения столбца витрины""" return pd.read_sql_query( f"select distinct {column} from dm.mart_order order by {column}", engine) @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_order_data( date_from = None, date_to = None, city = None, product = None ): """Получаем данные из витрины""" filter = '' if date_from: filter += f" and order_month>='{str(date_from)}'" if date_to: filter += f" and order_month<='{str(date_to)}'" if city: city = "','".join(city) filter += f" and city_name in ('{city}')" if product: product = "','".join(product) filter += f" and product_category in ('{str(product)}')" query = f""" select order_month, sum(total_orders) as orders_count from dm.mart_order where 1=1 {filter} group by order_month """ return pd.read_sql_query(query, engine) Python-скрипт from sqlalchemy import create_engine import pandas as pd import streamlit as st from streamlit_echarts import st_echarts import jinja2 import datetime import json from typing import Literal st.set_page_config(layout="wide") # Создаем движок подключения engine = create_engine('postgresql+psycopg2://postgres:admin@localhost:5432/database') # получаем и кэшируем данные spinner_text = "Чтение данных..." @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_unique_values(column): """Получаем уникальные значения столбца витрины""" return pd.read_sql_query( f"select distinct {column} from dm.mart_order order by {column}", engine) @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_extreme_value(column, type: Literal["min", "max"]): """Получаем экстремумы""" return pd.read_sql_query( f"select {type}({column}) from dm.mart_order", engine).iloc[0, 0] @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_order_data( date_from = None, date_to = None, city = None, product = None, price = None ): """Получаем данные из витрины""" with open('get_order_data.sql', "r", encoding="utf-8") as f: file = f.read() script = jinja2.Template(file).render( date_from=str(date_from) if date_from else None, date_to=str(date_to) if date_to else None, city=city, product=product, price=price ) return pd.read_sql_query(script, engine) @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_total_data( date_from = None, date_to = None, city = None, product = None, price = None ): """Получаем данные из витрины""" with open('get_total_data.sql', "r", encoding="utf-8") as f: file = f.read() script = jinja2.Template(file).render( date_from=str(date_from) if date_from else None, date_to=str(date_to) if date_to else None, city=city, product=product, price=price ) return pd.read_sql_query(script, engine) @st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text) def get_order_speed( date_from = None, date_to = None, city = None, product = None, last = False ): """Получаем данные из витрины""" with open('speed.sql', "r", encoding="utf-8") as f: file = f.read() script = jinja2.Template(file).render( date_from=str(date_from) if date_from else None, date_to=str(date_to) if date_to else None, city=city, product=product ) return pd.read_sql_query(script, engine) # фильтрация sidebar = st.sidebar col1, col2 = sidebar.columns((1,1)) with col1: from_date = st.date_input("От", value=None) with col2: to_date = st.date_input("До", value=None) city = sidebar.multiselect("Город", options=get_unique_values('city_name')) product = sidebar.multiselect("Продукт", options=get_unique_values('product_category')) price = sidebar.slider( "Цена", min_value=get_extreme_value("total_order_amount", 'min'), max_value=get_extreme_value("total_order_amount", 'max'), step=1.0 ) # метрики st.caption('За последний месяц') total_current_data = get_total_data( from_date, to_date, city, product, price=price ) col1, col2, col3, col4 = st.columns((1,1,1,1)) with col1: total_order = "{:,.0f}".format(total_current_data['total_orders'].tolist()[0]) st.metric("Кол-во заказов", value=total_order, delta=f"{total_current_data['delta_total_orders'].tolist()[0]} %") with col2: total_order_amount = "{:,.0f}".format(total_current_data['total_order_amount'].tolist()[0]) st.metric("Объем продаж", value=total_order_amount, delta=f"{total_current_data['delta_total_order_amount'].tolist()[0]} %") with col3: average_order_amount = "{:,.0f}".format(total_current_data['average_order_amount'].tolist()[0]) st.metric("Среднее кол-во позиций в заказе", value=average_order_amount, delta=f"{total_current_data['delta_average_order_amount'].tolist()[0]} %") with col4: returned_orders = "{:,.0f}".format(total_current_data['returned_orders'].tolist()[0]) st.metric("Кол-во возвратов", value=returned_orders, delta=f"{total_current_data['delta_returned_orders'].tolist()[0]} %", delta_color='inverse') # График df = get_order_data( from_date, to_date, city, product, price=price ) df['order_month'] = df['order_month'].apply(lambda x: x.strftime('%Y-%m')) last_speed_df = get_order_speed( from_date, to_date, city, product, last=True ) speed_df = get_order_speed( from_date, to_date, city, product ) speed_df['order_month'] = speed_df['order_month'].apply(lambda x: x.strftime('%Y-%m')) col1, col2 = st.columns((1,1)) with col1: with open('orders_by_month.json', "r", encoding="utf-8") as f: orders_by_month_json = f.read() orders_by_month_options = jinja2.Template(orders_by_month_json).render( order_month=df['order_month'].tolist(), orders_count=df['orders_count'].tolist(), average_order_amount=df['average_order_amount'].tolist() ) st_echarts(options=json.loads(orders_by_month_options), height="400px") with col2: with open('returned_by_month.json', "r", encoding="utf-8") as f: returned_by_month = f.read() returned_by_month_options = jinja2.Template(returned_by_month).render( order_month=df['order_month'].tolist(), returned_count=df['returned_count'].tolist(), returned_percent=df['returned_percent'].tolist(), max_count=df['orders_count'].max() ) st_echarts(options=json.loads(returned_by_month_options), height="400px") col1, col2 = st.columns((1,4)) with col1: with open('order_speed.json', "r", encoding="utf-8") as f: last_speed_json = f.read() last_speed_options = jinja2.Template(last_speed_json).render( speed=last_speed_df['avg_speed'].tolist()[0] ) st_echarts(options=json.loads(last_speed_options), height="400px") with col2: with open('order_speed_by_month.json', "r", encoding="utf-8") as f: speed_by_month_json = f.read() speed_by_month_options = jinja2.Template(speed_by_month_json).render( speed=speed_df['avg_speed'].tolist(), order_month=speed_df['order_month'].tolist() ) st_echarts(options=json.loads(speed_by_month_options), height="400px") Streamlit можно развернуть и на сервере, даже можно реализовать аутентификацию в интерфейсе и реализовать кэширование данных под конкретную сессию, но при большом количестве одновременных пользователей наверняка будут проблемы.

Author: PavelKhamrin

---