skyworth

--Office kb
select line_name,
order_number,
produced_units_today,
to_char(produced_units_ave_today,'fm9990.00') produced_units_ave_today,
to_char(labor_hour_ave_today,'fm9990.00') labor_hour_ave_today,
nvl(to_char(standard_units_today,'fm9990'),0),
first_pass_yield,nvl(alarm_equipment,0),
nvl（alarm_staff,0）, nvl(alarm_part,0),
nvl(alarm_quality,0),
nvl(loss_labor_hour,0),
to_char(working_time,'fm9990.00'),
accumulate_labor_hour,
to_char(produced_units_month,'fm9990'),
to_char(produced_units_ave_month,'fm9990.00'),
to_char(labor_hour_ave_month,'fm9990.00'),
nvl(trim(standard_units_month),0)
from kb_office where 1=1;


--Efficient kb
SELECT * FROM kb_prod_efficiency;
SELECT line_name, --线别
order_number, --工单号
machine_type, --机型
mechanism, --机芯
order_qty, --数量
order_input_qty, --投入数
order_output_qty, --产出数
order_qty_hour, --单小时产出
start_time, --开始时间
complete_time, --结束时间
order_accumulate_labor_hour, --工单累计工时
order_production_efficiency, --工单工时效率
takt_time, --标台节拍
position_balance_rate, --岗位平衡率
plan_achieve_rate, --计划达成率
accumulate_output, --累计产出
accumulate_defects, --‘’累计不良
accumulate_fpy, --累计直通
accumulate_labor_hour, --累计工时
line_production_efficiency, --产线工时效率
line_head_count, --产线人数
period_output, --时段产出
peirod_fpy --时段直通
FROM kb_prod_efficiency where 1=1;

--时段产出
select * from KB_PROD_HOURCHART;

--ORACLE 数据库时间转换格式
select to_char(START_TIME, ' HH24') from KB_PROD_HOURCHART

--ORACLE 数据库转换时间格式后按照降序形式输出
select to_char(START_TIME, ' HH24:mi') from KB_PROD_HOURCHART order by to_char(START_TIME, ' HH24:mi') // 加入 DESC后为升序，不加为降序

--to_char(round(PEIROD_FPY*100, 3),'9999999.9') || '%' 数据库数据转换为百分数 // 加了“，3”不会四舍五入

--where rownum=1 只取数据库表第一条数据

--quality kb
SELECT line_name,
Order_Number,
machine_type,
mechanism,
order_output_qty,
accumulate_defects,
first_pass_yield
SELECT accumulate_defects FROM kb_quality;

--获取不良现象种类top4
SELECT ROWNUM,
t.defect_catgory,
t.sum_qty
FROM (SELECT defect_catgory, SUM(DEFECT_QTY) sum_qty
FROM kb_quality_defitem
GROUP BY defect_catgory
ORDER BY sum_qty DESC) t
WHERE ROWNUM <= 4;

select sum(DEFECT_QTY),DEFECT_CATGORY from SKYWORTHUSER.KB_QUALITY_DEFITEM group by DEFECT_CATGORY （质量帕累托图分类后求和）

-------------------------

select DEFECT_CATGORY,x, Sum(x) over(Order By x) y from (select sum(DEFECT_QTY)x,DEFECT_CATGORY from SKYWORTHUSER.KB_QUALITY_DEFITEM group by DEFECT_CATGORY order by DEFECT_CATGORY) （递归求和）

-------------------------------

select (DEFECT_CATGORY)类型,(x)出现数量,y/(select sum(x) from (select DEFECT_CATGORY,x, Sum(x) over(Order By x desc) y from (select sum(DEFECT_QTY)x,DEFECT_CATGORY from SKYWORTHUSER.KB_QUALITY_DEFITEM group by DEFECT_CATGORY order by DEFECT_CATGORY)))百分比 from (select DEFECT_CATGORY,x, Sum(x) over(Order By DEFECT_CATGORY desc) y from (select sum(DEFECT_QTY)x,DEFECT_CATGORY from SKYWORTHUSER.KB_QUALITY_DEFITEM group by DEFECT_CATGORY order by DEFECT_CATGORY)) （帕累托图sql语句）

--------------------------------------------------------

出现sum求和后分组整除现象用子查询框住分母后即可使用

--------------------------------------------------------

ora-00937 不是分组单组函数（将不同于其他组的输出列group by 一下）

--------------------------------------------------------

数据表分当前看板内容的数据表和历史数据表。历史数据表是在当前看板内容数据表末尾加后缀_HIS。

--------------------------------------------------------

查询当天所有数据：1.select * from SKYWORHUSER.ITF_TACK_TIME where trunc(create_time) = trunc(sysdate) 2.select * from SKYWORHUSER.ITF_TACK_TIME where 时间变量 >= trunc(sysdate) and 时间变量 < trunc(sysdate) + 1

--------------------------------------------------------

工位节拍：

求当天节拍总数：select * from (select (station_code)x,sum(tack_time) from SKYWORTHUSER.ITF_TACK_TIME where create_time <= trunc(sysdate) and create_time > trunc(sysdate) - 1 group by station_code) order by x

求当天产生节拍行数：select (station_code)x,count(station_code) as z from (select * from SKYWORTHUSER.ITF_TACK_TIME where create_time <= trunc(sysdate) and create_time > trunc(sysdate) - 1 order by station_code) group by station_code order by station_code

总和：select (station_code)x,sum(tack_time)y,count(station_code) as z from (select * from SKYWORTHUSER.ITF_TACK_TIME where create_time <= trunc(sysdate) and create_time > trunc(sysdate) - 1 order by station_code) group by station_code order by station_code

生产节拍综合：select 工位号,节拍数/工位行数 from (select (station_code)工位号,sum(tack_time)节拍数,count(station_code) as 工位行数 from (select * from SKYWORTHUSER.ITF_TACK_TIME where trunc(create_time) = trunc(sysdate) order by station_code) group by station_code order by station_code) where rownum <=28 order by 工位号

生产节拍大小值数据综合：select max(工位平均节拍), min(工位平均节拍),avg(工位平均节拍) from (select 工位号,(节拍数/工位行数)工位平均节拍 from (select (station_code)工位号,sum(tack_time)节拍数,count(station_code) as 工位行数 from (select * from SKYWORTHUSER.ITF_TACK_TIME where trunc(create_time) = trunc(sysdate) order by station_code) group by station_code order by station_code) order by 工位号) where rownum <=28

生产节拍最大值对应工位：select (工位号)最大节拍工位号 from (select cast(工位号 as smallint) as 工位号,(节拍数/工位行数) as 各工位节拍 from (select (station_code)工位号,sum(tack_time)节拍数,count(station_code) as 工位行数 from (select * from SKYWORTHUSER.ITF_TACK_TIME where trunc(create_time) = trunc(sysdate) order by station_code) group by station_code order by station_code) where rownum <=28 order by 工位号) where 各工位节拍 = (select max(工位平均节拍) as max from (select 工位号,(节拍数/工位行数)工位平均节拍 from (select (station_code)工位号,sum(tack_time)节拍数,count(station_code) as 工位行数 from (select * from SKYWORTHUSER.ITF_TACK_TIME where trunc(create_time) = trunc(sysdate) order by station_code) group by station_code order by station_code) order by 工位号) where rownum <=28)

--------------------------------------------------------

字符串转换为数字类型：cast(字段 as smallint）

--------------------------------------------------------

数字转换为整形去掉前缀：select convert(int,字段)

--------------------------------------------------------

sql语句保留两位小数：round(变化量,2)

--------------------------------------------------------

生产效率看板数据 :

SKYWORTHUSER.KB_PROD_EFFICIENCY

生产效率看板的时间段产出，直通率数据 SKYWORTHUSER.KB_PROD_HOURCHART

办公室看板数据 SKYWORTHUSER.KB_OFFICE

品质看板数据 SKYWORTHUSER.KB_QUALITY

品质看板中不良数据 SKYWORTHUSER.KB_QUALITY_DEFITEM

异常看板数据 SKYWORTHUSER.KB_ALARM

SKYWORTHUSER.KB_EQUIPMENT
设备看板数据
历史数据表:
SKYWORTHUSER.KB_PROD_EFFICIENCY_HIS
SKYWORTHUSER.KB_PROD_HOURCHART_HIS
SKYWORTHUSER.KB_OFFICE_HIS
SKYWORTHUSER.KB_QUALITY_HIS
SKYWORTHUSER.KB_QUALITY_DEFITEM_HIS
SKYWORTHUSER.KB_ALARM_HIS
SKYWORTHUSER.KB_EQUIPMENT_HIS

-----------------------------------------------

max(岗位平衡图表.select("节拍"))不同颜色显示柱子