求思路，几十W数据与另一个表8W条，进行联合查询，进行单位换算

2015-01-23 02:03:08 357 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

用LTspice仿真CMOS反相器功耗？这份避坑指南能省你3天调试时间本文详细介绍了如何使用LTspice准确仿真CMOS反相器的功耗，包括动态功耗、短路功耗和静态功耗的测量技巧。通过工程实践指南，帮助电子工程师避开常见陷阱，优化仿真参数设置，提升仿真结果的可靠性，节省调试时间。阅读详情

有一个库存变化表A，里面有至少几十W条数据
ItemID TransDate InventUnit PurchUnit SalesUnit Qty CostUnit InventFactor PurchFactor SalesFactor
A001 2014/10/01 kg g kg 100 kg
A001 2014/10/02 kg g kg 200 kg
B001 2014/10/01 kg g kg 100 kg
B001 2014/10/02 kg g kg 200 kg

单位换算表B
ItemID FromUnit ToUnit Factor
A001 kg g 1000
A001 g kg 0.001

现在要分别对A表的 InventUnit ，PurchUnit， SalesUnit 与 CostUnit 对比，相同则=1，不同则到表B去检索，得到 InventFactor PurchFactor SalesFactor

如果单纯通过外联，效率非常低



select  A.ItemId,

case when  A.InventUnit = A.CostUnit then 1 else  B1.Factor end as InventFactor，

case when  A.PurchUnit = A.CostUnit then 1 else  B2.Factor end as PurchFactor，

case when  A.SalesUnit = A.CostUnit then 1 else  B3.Factor end as SalesFactor

from A 

left join B B1

on A.ItemId = B1.ItemID

and A.InventUnit = B1.FromUnit

and A.CostUnit = B1.ToUnit

left join B B2

on A.ItemId = B2.ItemID

and A.InventUnit = B2.FromUnit

and A.CostUnit = B2.ToUnit

left join B B3

on A.ItemId = B3.ItemID

and A.InventUnit = B3.FromUnit

and A.CostUnit = B3.ToUnit

后来想了一下，因为A表ItemId有很多重复，所以想先对A的ItemId进行GroupBy，得到A1表
在跟B进行外联，作为C表，然后A跟C内联，得到 3个单位换算系数

我这么想的，各位大大还有什么更好的方法没？

...全文

【AIOPS实战】Dify+Zabbix：构建智能告警分析助手的核心架构与实现本文详细介绍了如何利用Dify平台与Zabbix-MCP接口构建智能告警分析助手，实现自然语言查询告警、智能分析和报告生成。该系统通过AIOPS技术显著提升运维效率，支持中英文混合查询、自动根因分析，并内置多种优化方案确保稳定可靠。阅读详情

从不签到

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

Tiger_Zhao 2015-01-26

打赏
举报

A)在你做 INSERT/UPDATE 之前先把换算率查出来，包含在插入/更新中。
B)用触发器，把插入/更新相关行的换算率查出来，更新到记录中。
这样处理的数据量不大，从B1、B2查询的范围也小，速度够快。等于把批量更新的耗时分散到平时的操作中，感觉不到性能的影响。

从不签到 2015-01-26

打赏
举报

B的View是由B1，B2组成的 B1保存的是单位，B2保存的是换算率，通过RecId进行关联的所以查询的时候，得到2个表去找，跟view是一样的建议直接把单位换算率在A表新增、修改数据时带上换算率这个怎么弄？

alongkuku 2015-01-23

打赏
举报

如果B表的数据是不会变的，建议直接把单位换算率在A表新增、修改数据时带上换算率。如果变化，建议索引连接join即可。

Tiger_Zhao 2015-01-23

打赏
举报

不要View，去关联原始的转换表啊！！！

从不签到 2015-01-23

打赏
举报

B表已经是View了，8w条数据上面的方法执行的话， 15条数据跑了很长时间，没耐心等，就停了。。。

Tiger_Zhao 2015-01-23

打赏
举报

...

,sB (--筛选出用到的单位换算

    SELECT sA.ItemID,

           ISNULL(B1.FromUnit,B0.FromUnit) FromUnit,

           ISNULL(B1.ToUnit,B0.ToUnit) ToUnit,

           ISNULL(B1.Factor,B0.Factor) Factor

      FROM sA

 LEFT JOIN B1

        ON B1.ItemID = sA.ItemID

       AND B1.ToUnit = sA.CostUnit

       AND (   B1.FromUnit = sA.InventUnit

            OR B1.FromUnit = sA.PurchUnit

            OR B1.FromUnit = sA.SalesUnit

           )

 LEFT JOIN B0

        ON B0.ItemID IS NULL

       AND B0.ToUnit = sA.CostUnit

       AND (   B0.FromUnit = sA.InventUnit

            OR B0.FromUnit = sA.PurchUnit

            OR B0.FromUnit = sA.SalesUnit

           )

)

...

这样应该比先做个全部ItemID的视图数据量小吧。

从不签到 2015-01-23

打赏
举报

表B的构造是这样的单位换算表B ItemID FromUnit ToUnit Factor Product NULL kg g 1000 0 NULL g kg 0.001 0 A001 kg 袋 2 12345678 像 kg = 1000g等共通的变换， ItemId = NULL， Product = 0 其余的则有ItemID 上面的例子不太好

Tiger_Zhao 2015-01-23

打赏
举报

转换率不应该区分ItemID的吧，1kg=1000g应该是不变的啊！

从不签到 2015-01-23

打赏
举报

其实表B是个3个表关联起来的view，不用view，每次写3个表我觉得麻烦现在3300条数据，按照我之前的方法，跑了10多分钟没跑完，我就停下了

LongRui888 2015-01-23

打赏
举报

楼上的也是好方法。为了减少join的开销，可以考虑冗余字段，也就是直接把 factor放到库存变化表。

LongRui888 2015-01-23

打赏
举报

这种还是比较难优化的，其实这种情况，应该在B表多一个主键字段，然后再A表中直接存储找个字段，用来关联，现在是必须要同时用3个字段来关联。现在运行要多久呢？

Tiger_Zhao 2015-01-23

打赏
举报

WITH sA AS (--筛选出A中的单位

    SELECT DISTINCT ItemID, InventUnit, PurchUnit, SalesUnit, CostUnit

      FROM A

     WHERE InventFactor IS NULL

        OR PurchFactor IS NULL

        OR SalesFactor IS NULL

)

,sB (--筛选出用到的单位换算

    SELECT B.*

      FROM B

      JOIN sA

        ON B.ItemID = sA.ItemID

       AND B.ToUnit = sA.CostUnit

       AND (   B.FromUnit = sA.InventUnit

            OR B.FromUnit = sA.PurchUnit

            OR B.FromUnit = sA.SalesUnit

           )

)

    UPDATE A

       SET A.InventFactor = ISNULL(b1.Factor,1),

           A.PurchFactor = ISNULL(b2.Factor,1),

           A.SalesFactor = ISNULL(b3.Factor,1)

      FROM A

 LEFT JOIN sB b1

        ON A.ItemID = b1.ItemID

        -- A.ConstUnit 已经在 sB 中过滤过了，不用写关联了。

       AND A.InventUnit = b1.FromUnit

 LEFT JOIN sB b2

        ON A.ItemID = b2.ItemID

       AND A.PurchUnit = b2.FromUnit

 LEFT JOIN sB b3

        ON A.ItemID = b3.ItemID

       AND A.SalesUnit = b3.FromUnit

     WHERE A.InventFactor IS NULL

        OR A.PurchFactor IS NULL

        OR A.SalesFactor IS NULL

可以这样试试，不过最好是插入数据时就加上换算率，否则一次性更新几十W日志都很多，更不要说性能了。

还在加载中灬 2015-01-23

打赏
举报

可以考虑建索引，建议按这个顺序建覆盖索引 A表，ItemId CostUnit B表，ItemID ToUnit FromUnit 然后连接顺序按索引列的顺序及减少连接次数

select  A.ItemId,
ISNULL(B1.Factor,1)as InventFactor,
ISNULL(B2.Factor,1)as PurchFactor,
ISNULL(B3.Factor,1)as SalesFactor
from A 
left join B B1
on A.ItemId = B1.ItemID AND A.CostUnit<>A.InventUnit
and A.CostUnit = B1.ToUnit
and A.InventUnit = B1.FromUnit
left join B B2
on A.ItemId = B2.ItemID AND A.CostUnit<>A.PurchUnit
and A.CostUnit = B2.ToUnit
and A.PurchUnit = B2.FromUnit
left join B B3
on A.ItemId = B3.ItemID AND A.CostUnit<>A.SalesUnit
and A.CostUnit = B3.ToUnit
and A.SalesUnit = B3.FromUnit

因为你的查询就表关联查询比较多，我想上索引可能会快些

求思路，几十W数据与另一个表8W条，进行联合查询，进行单位换算

相关推荐