freedakgmail
ed7beb7e47
- 创建SearchSelect搜索下拉组件，选项≤8自动隐藏搜索框 - 全站替换原生select为SearchSelect（13处下拉） - 修复/revenue/store-ranking优惠率字段名不匹配(discount_rate_pct→avg_discount_rate_pct) - RevenuePage门店营收明细改用FilterableTable支持筛选排序分页 - 修复/marketing/plans按门店分组改为按marketing_plan分组 - 营销方案分析补充消费额、优惠额列和Top5概览卡片 - PlatformPage门店平台经济性增加门店搜索、经济性优列(平台分色) - 平台成本率算法说明显示在标题下方
54 lines
4.8 KiB
SQL
54 lines
4.8 KiB
SQL
DROP MATERIALIZED VIEW IF EXISTS mv_time_weekday;
|
|
DROP MATERIALIZED VIEW IF EXISTS mv_time_hourly;
|
|
DROP MATERIALIZED VIEW IF EXISTS mv_channel_daily;
|
|
|
|
CREATE MATERIALIZED VIEW mv_time_weekday AS
|
|
SELECT
|
|
EXTRACT(isodow FROM c176::timestamp)::int AS weekday_no,
|
|
CASE EXTRACT(isodow FROM c176::timestamp)::int
|
|
WHEN 1 THEN '周一' WHEN 2 THEN '周二' WHEN 3 THEN '周三'
|
|
WHEN 4 THEN '周四' WHEN 5 THEN '周五' WHEN 6 THEN '周六' ELSE '周日'
|
|
END AS weekday,
|
|
count(*) AS bill_count,
|
|
round(sum(COALESCE(c143::numeric,0)+COALESCE(c144::numeric,0)+COALESCE(c145::numeric,0)+COALESCE(c146::numeric,0)+COALESCE(c147::numeric,0)+COALESCE(c148::numeric,0)+COALESCE(c149::numeric,0)+COALESCE(c150::numeric,0)+COALESCE(c151::numeric,0)+COALESCE(c152::numeric,0)+COALESCE(c153::numeric,0)+COALESCE(c155::numeric,0)+COALESCE(c160::numeric,0)+COALESCE(c161::numeric,0)+COALESCE(c164::numeric,0)+COALESCE(c166::numeric,0)+COALESCE(c169::numeric,0)+COALESCE(c173::numeric,0)+COALESCE(c174::numeric,0)+COALESCE(c118::numeric,0)+COALESCE(c119::numeric,0)+COALESCE(c120::numeric,0)+COALESCE(c121::numeric,0)+COALESCE(c139::numeric,0)+COALESCE(c141::numeric,0)+COALESCE(c142::numeric,0)),2) AS received,
|
|
round(sum(COALESCE(c143::numeric,0)+COALESCE(c144::numeric,0)+COALESCE(c145::numeric,0)+COALESCE(c146::numeric,0)+COALESCE(c147::numeric,0)+COALESCE(c148::numeric,0)+COALESCE(c149::numeric,0)+COALESCE(c150::numeric,0)+COALESCE(c151::numeric,0)+COALESCE(c152::numeric,0)+COALESCE(c153::numeric,0)+COALESCE(c155::numeric,0)+COALESCE(c160::numeric,0)+COALESCE(c161::numeric,0)+COALESCE(c164::numeric,0)+COALESCE(c166::numeric,0)+COALESCE(c169::numeric,0)+COALESCE(c173::numeric,0)+COALESCE(c174::numeric,0)+COALESCE(c118::numeric,0)+COALESCE(c119::numeric,0)+COALESCE(c120::numeric,0)+COALESCE(c121::numeric,0)+COALESCE(c139::numeric,0)+COALESCE(c141::numeric,0)+COALESCE(c142::numeric,0)) / count(*), 2) AS avg_bill_value,
|
|
round(avg(EXTRACT(epoch FROM (c176::timestamp - c175::timestamp))/60), 2) AS avg_duration_minutes,
|
|
to_char(c176::timestamp, 'YYYY-MM') AS month
|
|
FROM bill_records
|
|
WHERE c176 IS NOT NULL AND c176 != '' AND c175 IS NOT NULL AND c175 != ''
|
|
GROUP BY 1, 2, to_char(c176::timestamp, 'YYYY-MM');
|
|
|
|
CREATE MATERIALIZED VIEW mv_time_hourly AS
|
|
SELECT
|
|
EXTRACT(hour FROM c176::timestamp)::int AS closing_hour,
|
|
count(*) AS bill_count,
|
|
round(sum(COALESCE(c143::numeric,0)+COALESCE(c144::numeric,0)+COALESCE(c145::numeric,0)+COALESCE(c146::numeric,0)+COALESCE(c147::numeric,0)+COALESCE(c148::numeric,0)+COALESCE(c149::numeric,0)+COALESCE(c150::numeric,0)+COALESCE(c151::numeric,0)+COALESCE(c152::numeric,0)+COALESCE(c153::numeric,0)+COALESCE(c155::numeric,0)+COALESCE(c160::numeric,0)+COALESCE(c161::numeric,0)+COALESCE(c164::numeric,0)+COALESCE(c166::numeric,0)+COALESCE(c169::numeric,0)+COALESCE(c173::numeric,0)+COALESCE(c174::numeric,0)+COALESCE(c118::numeric,0)+COALESCE(c119::numeric,0)+COALESCE(c120::numeric,0)+COALESCE(c121::numeric,0)+COALESCE(c139::numeric,0)+COALESCE(c141::numeric,0)+COALESCE(c142::numeric,0)),2) AS received,
|
|
round(sum(COALESCE(c143::numeric,0)+COALESCE(c144::numeric,0)+COALESCE(c145::numeric,0)+COALESCE(c146::numeric,0)+COALESCE(c147::numeric,0)+COALESCE(c148::numeric,0)+COALESCE(c149::numeric,0)+COALESCE(c150::numeric,0)+COALESCE(c151::numeric,0)+COALESCE(c152::numeric,0)+COALESCE(c153::numeric,0)+COALESCE(c155::numeric,0)+COALESCE(c160::numeric,0)+COALESCE(c161::numeric,0)+COALESCE(c164::numeric,0)+COALESCE(c166::numeric,0)+COALESCE(c169::numeric,0)+COALESCE(c173::numeric,0)+COALESCE(c174::numeric,0)+COALESCE(c118::numeric,0)+COALESCE(c119::numeric,0)+COALESCE(c120::numeric,0)+COALESCE(c121::numeric,0)+COALESCE(c139::numeric,0)+COALESCE(c141::numeric,0)+COALESCE(c142::numeric,0)) / count(*), 2) AS avg_bill_value,
|
|
round(avg(EXTRACT(epoch FROM (c176::timestamp - c175::timestamp))/60), 2) AS avg_duration_minutes,
|
|
to_char(c176::timestamp, 'YYYY-MM') AS month
|
|
FROM bill_records
|
|
WHERE c176 IS NOT NULL AND c176 != '' AND c175 IS NOT NULL AND c175 != ''
|
|
GROUP BY 1, to_char(c176::timestamp, 'YYYY-MM');
|
|
|
|
CREATE MATERIALIZED VIEW mv_channel_daily AS
|
|
SELECT
|
|
c176::date AS business_date,
|
|
round(sum(c143::numeric),2) AS cash,
|
|
round(sum(c144::numeric),2) AS alipay,
|
|
round(sum(c145::numeric),2) AS wechat,
|
|
round(sum(c146::numeric),2) AS meituan,
|
|
round(sum(c147::numeric),2) AS unionpay,
|
|
round(sum(c148::numeric),2) AS douyin,
|
|
round(sum(c149::numeric),2) AS credit,
|
|
round(sum(c150::numeric),2) AS jd_delivery,
|
|
round(sum(c151::numeric),2) AS meituan_delivery,
|
|
round(sum(c152::numeric),2) AS taobao_delivery,
|
|
to_char(c176::timestamp, 'YYYY-MM') AS month
|
|
FROM bill_records
|
|
WHERE c176 IS NOT NULL AND c176 != ''
|
|
GROUP BY 1, to_char(c176::timestamp, 'YYYY-MM');
|
|
|
|
CREATE INDEX idx_mv_weekday_month ON mv_time_weekday (month);
|
|
CREATE INDEX idx_mv_hourly_month ON mv_time_hourly (month);
|
|
CREATE INDEX idx_mv_channel_month ON mv_channel_daily (month);
|