前言:
DBeaver使用postgreSQL处理数据库并在tableau中进行可视化分析,从库存库容数据库中获取数据,分析仓库中的库位库容使用情况,尤其关注是否存在爆库问题发生。
注:仅用于此项目背景下的特定情况,其他情况可能并不适用
一、数据库处理代码
--table1表一 基础表
with table1 as (
select
inventory_lock_code,
dsp_locn,
item_id,
item_name,
"Code ID",
work_area,
cds_update_time,
valid_to,
SUBSTRING(dsp_locn FROM 1 FOR 5) AS extracted_string,
on_hand_qty,
case when on_hand_qty > 0 then 1 else 0 end as ware_house,
1 as sum_ware_house
from
bin_capacity.f_bin_material_capacity
),
--table2表二 锁的类型
table2 as (
select
"Code ID",
"Required Additional Parameters"
from
bin_capacity.lock_type
),
--table3表三 p12
table3 as (
select
work_area,
p12_ratio
from bin_capacity.d_bin_type
),
--基础表+锁的类型
Selected1_table as (
select
inventory_lock_code,
dsp_locn,
item_id,
item_name,
table1."Code ID",
work_area,
cds_update_time,
valid_to,
SUBSTRING(dsp_locn FROM 1 FOR 5) AS extracted_string,
"Required Additional Parameters",
on_hand_qty,
ware_house,
sum_ware_house
from
table1
--union all
left join
table2
on table1."Code ID" = table2."Code ID"
),
--基础表+锁的类型+p12
Selected2_table as (
select
inventory_lock_code,
dsp_locn,
item_id,
item_name,
Selected1_table."Code ID",
Selected1_table.work_area,
cds_update_time,
valid_to,
SUBSTRING(dsp_locn FROM 1 FOR 2) AS extracted_string,
on_hand_qty,
ware_house,
sum_ware_house,
"Required Additional Parameters",
p12_ratio
from
Selected1_table
left join
table3
on Selected1_table.work_area = table3.work_area
)
--数据透视(库位诗句重复,p12)
--select
-- *
--from
-- Selected2_table
SELECT
inventory_lock_code,
dsp_locn,
item_id,
item_name,
"Code ID",
work_area,
cds_update_time,
valid_to,
SUBSTRING(dsp_locn FROM 1 FOR 2) AS extracted_string,
SUM(ware_house) AS total_ware_house,
SUM(sum_ware_house) AS total_sum_ware_house,
"Required Additional Parameters",
sum(p12_ratio::NUMERIC) as p12
FROM
Selected2_table
where
not cds_update_time is null
GROUP BY
inventory_lock_code,
dsp_locn,
item_id,
item_name,
"Code ID",
work_area,
cds_update_time,
valid_to,
extracted_string,
"Required Additional Parameters"
二、Tableau绘图
使用Tableau绘图:
(注:数据经过筛选和处理,不代表任何信息)
总结:
技术栈:postgreSQL、Tableau
首先在dbeaver中用postgreSQL对数据库信息进行筛选和处理,获取到需要的信息,再将得到的数据在Tableau中进行可视化分析,得到最终展示的结果图。