可视化

看板系统/数据分析系统是两个东西

CREATE TABLE "产品表" (
	"产品ID" TEXT NULL DEFAULT NULL,
	"类别" TEXT NULL DEFAULT NULL,
	"子类别" TEXT NULL DEFAULT NULL,
	"产品名称" TEXT NULL DEFAULT NULL,
	"id" INTEGER NOT NULL,
	PRIMARY KEY ("id")
)
;
CREATE TABLE "客户表" (
	"客户ID" TEXT NULL DEFAULT NULL,
	"客户名称" TEXT NULL DEFAULT NULL,
	"细分" TEXT NULL DEFAULT NULL,
	"城市" TEXT NULL DEFAULT NULL,
	"省" TEXT NULL DEFAULT NULL,
	"国家" TEXT NULL DEFAULT NULL,
	"地区" TEXT NULL DEFAULT NULL,
	"id" INTEGER NOT NULL,
	PRIMARY KEY ("id")
)
;
CREATE TABLE "订单表" (
	"id" INTEGER NOT NULL,
	"订单 ID" TEXT NULL DEFAULT NULL,
	"订单日期" TIMESTAMP NULL DEFAULT NULL,
	"发货日期" TIMESTAMP NULL DEFAULT NULL,
	"邮寄方式" TEXT NULL DEFAULT NULL,
	"客户 ID" TEXT NULL DEFAULT NULL,
	"产品 ID" TEXT NULL DEFAULT NULL,
	"销售额" DOUBLE PRECISION NULL DEFAULT NULL,
	"数量" BIGINT NULL DEFAULT NULL,
	"折扣" DOUBLE PRECISION NULL DEFAULT NULL,
	"利润" DOUBLE PRECISION NULL DEFAULT NULL,
	"pid" INTEGER NULL DEFAULT NULL,
	"cid" INTEGER NULL DEFAULT NULL,
	"oid" INTEGER NULL DEFAULT NULL,
	PRIMARY KEY ("id")
)
;

基于 Tableau超市数据分析

select  
订单表."id",
订单表."订单日期"  ,
	订单表."发货日期" ,
	订单表."邮寄方式"  ,
	订单表."销售额"  ,
	订单表."数量"  ,
	订单表."折扣"  ,
	订单表."利润" ,
	客户表."客户名称" ,
	客户表."细分"  ,
	客户表."城市"  ,
	客户表."省" ,
	客户表."国家"  ,
	客户表."地区"  ,
	产品表."类别" ,
	产品表."子类别"  ,
	产品表."产品名称"  ,
from 订单表
left join 产品表 on (订单表.pid =产品表.id )
left join 客户表 on (订单表.cid =客户表.id )

累计销售额

SELECT  EXTRACT(YEAR FROM 订单日期) AS year,
	ROUND( sum( 销售额 )) as 年累计销售额, ROUND( sum( 利润 )) as 年利润,
	ROUND( (sum( 利润 )/( sum( 销售额 ) - sum( 利润 )) * 100)::numeric ,2  )   as 利润率,
	count( distinct id ) as 订单数,
	ROUND( sum( 销售额 ) / count( distinct id )) as  客单价,
	round((sum( 销售额)/  sum(销售额/(1-折扣)  )*100 )::numeric,2) as 年折扣率
FROM public."超市数据"
group by year 
order by  year

地区/类别/细分占比

SELECT   地区,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 地区 
order by  年累计销售额 

SELECT   类别,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 类别
order by  年累计销售额 

SELECT   细分,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 细分
order by  年累计销售额

同比销售

select t1.year as 年, (t1.年累计销售额/t2.年累计销售额-1)*100  同比
--, t1.年累计销售额 ,t2.年累计销售额 as 去年销售额
from 
(SELECT  EXTRACT(YEAR FROM 订单日期)   AS year,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by year ) t1
left join ( SELECT  EXTRACT(YEAR FROM 订单日期) + 1 AS 去年,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 去年 
  ) as t2
on t1.year = t2.去年
  order by  t1.year

月同比

select CONCAT(ta.年 ,'-', ta.月 ) as 时间
, ta.年累计销售额
, ROUND( ((ta.年累计销售额/tb.年累计销售额-1)*100)::numeric ) as 同比 
from 
(SELECT  EXTRACT(YEAR FROM 订单日期) AS 年, EXTRACT(month FROM 订单日期) AS 月,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 年 ,月
) ta
left join 
(SELECT  EXTRACT(YEAR FROM 订单日期) + 1  AS 年, EXTRACT(month FROM 订单日期) AS 月,
	ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 年 ,月
) tb
on ( ta.年 = tb.年 and ta.月 = tb.月)

order by  ta.年,ta.月

销售额组占比

select ta.*   
,round(ta.组内数量::NUMERIC/tb.组内数量*100  ) as 占比
from (SELECT  
EXTRACT(YEAR FROM 订单日期) AS 年,
 CASE 
        WHEN 销售额 <= 100 THEN '100'
        WHEN 销售额 <= 500 THEN '100-500'
        WHEN 销售额 <= 1000 THEN '500-1000'
        WHEN 销售额 <= 2000 THEN '1000-2000'
        ELSE '2000以上'
    END AS 范围组
	,count(销售额) 组内数量
	FROM public."超市数据"
group by 年 ,范围组) ta
left join  (SELECT  
	EXTRACT(YEAR FROM 订单日期) AS 年,
	count(销售额) 组内数量
	FROM public."超市数据"
	group by 年  
) tb
on ta.年  = tb.年 
order by  ta.年 ,范围组

均价

SELECT   CONCAT( EXTRACT(YEAR FROM 订单日期) ,'-', EXTRACT(month FROM 订单日期) ) as 时间
 ,EXTRACT(YEAR FROM 订单日期) AS 年
 , EXTRACT(month FROM 订单日期) AS 月 
 , round( avg(销售额)) as 销售均价
FROM public."超市数据"
group by 年 ,月
order by 年 ,月

地区环比

select ta.* ,ta.年累计销售额 
--, tb.去年累计销售额
, round( ((ta.年累计销售额 / tb.去年累计销售额-1)*100)::numeric ,2)
from (SELECT   
   EXTRACT(YEAR FROM 订单日期) AS 年
   ,地区
   ,ROUND( sum( 销售额 )) as 年累计销售额 
FROM public."超市数据"
group by 年,地区 
) ta
left join (SELECT   
   EXTRACT(YEAR FROM 订单日期) +1 AS 年
   ,地区
	,ROUND( sum( 销售额 )) as 去年累计销售额 
FROM public."超市数据"
group by 年,地区 
) tb
on (ta.年 = tb.年 and ta.地区 = tb.地区)
order by  ta.年,ta.年累计销售额 desc

类别top

大数据理念-嵌套

WITH 产品销售额 AS (
    SELECT
	EXTRACT(YEAR FROM 订单日期) AS 年,
        类别,
        子类别,
        产品名称,
        round(SUM(销售额) )AS 产品总销售额
    FROM public."超市数据"
    GROUP BY 年,类别, 子类别, 产品名称
),
排名数据 AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY 年,类别  
            ORDER BY 产品总销售额 DESC
        ) AS 排名
    FROM 产品销售额
)
SELECT *
FROM 排名数据
WHERE 排名 <= 2
ORDER BY 年,类别, 产品总销售额 desc, 排名;

客单价

SELECT  
城市 , count(distinct 客户名称  ) 客户数
, round(sum(销售额)/count(distinct 客户名称  ) ) as 客单价
, round(avg(销售额)  ) as 均价
FROM public."超市数据" 
group by 城市
order by 客户数 desc

SELECT  EXTRACT(YEAR FROM 订单日期) AS year,
EXTRACT(MONTH FROM 订单日期) AS month 
FROM public."超市数据"
group by year,month
order by  year,month


COMMENT ON COLUMN "产品表"."产品 ID" IS '';
COMMENT ON COLUMN "产品表"."类别" IS '';
COMMENT ON COLUMN "产品表"."子类别" IS '';
COMMENT ON COLUMN "产品表"."产品名称" IS '';
COMMENT ON COLUMN "产品表"."id" IS '';
COMMENT ON COLUMN "客户表"."客户 ID" IS '';
COMMENT ON COLUMN "客户表"."客户名称" IS '';
COMMENT ON COLUMN "客户表"."细分" IS '';
COMMENT ON COLUMN "客户表"."城市" IS '';
COMMENT ON COLUMN "客户表"."省" IS '';
COMMENT ON COLUMN "客户表"."国家" IS '';
COMMENT ON COLUMN "客户表"."地区" IS '';
COMMENT ON COLUMN "客户表"."id" IS '';
COMMENT ON COLUMN "订单表"."id" IS '';
COMMENT ON COLUMN "订单表"."订单 ID" IS '';
COMMENT ON COLUMN "订单表"."订单日期" IS '';
COMMENT ON COLUMN "订单表"."发货日期" IS '';
COMMENT ON COLUMN "订单表"."邮寄方式" IS '';
COMMENT ON COLUMN "订单表"."客户 ID" IS '';
COMMENT ON COLUMN "订单表"."产品 ID" IS '';
COMMENT ON COLUMN "订单表"."销售额" IS '';
COMMENT ON COLUMN "订单表"."数量" IS '';
COMMENT ON COLUMN "订单表"."折扣" IS '';
COMMENT ON COLUMN "订单表"."利润" IS '';
COMMENT ON COLUMN "订单表"."pid" IS '';
COMMENT ON COLUMN "订单表"."cid" IS '';
COMMENT ON COLUMN "订单表"."oid" IS '';

现工作文档