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

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)

Есть pandas.dataframe со столбцами order_month, orders_count и average_order_amount. Сформируй json с параметрами для графика streamlit_echarts , по оси X orders_month, по оси Y orders_count с типом bar, цвет orders_count — #1E90FF. По дополнительной оси Y average_order_amount c типом line, цвет — #B0E0E6. У графика должен быть title — Заказы по месяцам.

Ответ ChatGPT

{     "title": {         "text": "Заказы по месяцам",         "left": "center",         "textStyle": {             "color": "#FFFFFF"  # Цвет заголовка         }     },     "tooltip": {         "trigger": "axis"     },     "legend": {         "data": ["Количество заказов", "Средний чек"],          "bottom": "0%",   # Легенда внизу          "left": "center", # Выравнивание по центру по горизонтали          "orient": "horizontal",  # Горизонтальная ориентация         "textStyle": {             "color": "#FFFFFF"  # Цвет заголовка         }     },     "xAxis": {         "type": "category",         "data": df['order_month'].tolist()     },     "yAxis": [         {             "type": "value",             "name": "Количество заказов"         },         {             "type": "value",             "name": "Средний чек",             "position": "right"         }     ],     "series": [         {             "name": "Количество заказов",             "type": "bar",             "data": df['orders_count'].tolist(),             "itemStyle": {                 "color": "#1E90FF"             },             "yAxisIndex": 0         },         {             "name": "Средний чек",             "type": "line",             "data": df['average_order_amount'].tolist(),             "itemStyle": {                 "color": "#B0E0E6"             },             "yAxisIndex": 1         }     ] } 

select     order_month,     sum(total_orders) as orders_count,     sum(total_order_amount)/sum(total_orders) as average_order_amount from dm.mart_order where 1=1     {% if date_from %}and order_month>='{{ date_from }}'{% endif %}     {% if date_to %}and order_month<='{{ date_to }}'{% endif %}     {% if city %}and city_name in ({% for item in city %}'{{ item }}'{% if not loop.last %},{% endif %}{% endfor %}){% endif %}     {% if product %}and product_category in ({% for item in product %}'{{ item }}'{% if not loop.last %},{% endif %}{% endfor %}){% endif %} group by     order_month

@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 ):     """Получаем данные из витрины"""     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     )     st.write(script)     return pd.read_sql_query(script, engine)

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")