在每个分组中使用Presto或MySQL添加缺失值。

huangapple
go评论160阅读模式
英文:

Add missing values in each group using presto or mysql

问题

我有以下的表格：

  1. prod_name - quantity - group
  2. tv        - 30      - base 
  3. microwave - 10      - base 
  4. watch     - 5       - base  
  5. phone     - 25      - base 
  6. washer    - 15      - base 
  7. dryer.    - 14      - base  
  8. microwave - 7       - inventory_2021 
  9. phone     - 16      - inventory_2021  
  10. tv        - 30      - inventory_2022 
  11. watch     - 5       - inventory_2022 
  12. phone     - 25      - inventory_2022

我想要将缺失的记录从base(group)填充/输入到所有其他组中。

期望的输出是：

  1. tv        - 30      - base 
  2. microwave - 10      - base 
  3. watch     - 5       - base 
  4. phone     - 25      - base 
  5. washer    - 15      - base 
  6. dryer.    - 14      - base 
  7. tv        - 30      - inventory_2021 
  8. microwave - 7       - inventory_2021 
  9. watch     - 5       - inventory_2021 
  10. phone     - 16      - inventory_2021 
  11. washer    - 15      - inventory_2021 
  12. dryer.    - 14      - inventory_2021 
  13. tv        - 30      - inventory_2022 
  14. microwave - 10      - inventory_2022 
  15. watch     - 5       - inventory_2022 
  16. phone     - 25      - inventory_2022 
  17. washer    - 15      - inventory_2022 
  18. dryer.    - 14      - inventory_2022

因此，在输出中，每个组中有相同数量的条目，总条目数应与基础组匹配。

英文:

I have a following table :

  1. prod_name - quantity - group
  2. tv        - 30  - base 
  3. microwave - 10  - base 
  4. watch     - 5   - base  
  5. phone     - 25  - base 
  6. washer    - 15  - base 
  7. dryer.    - 14  - base  
  8. microwave - 7   - inventory_2021 
  9. phone     - 16  - inventory_2021  
  10. tv        - 30  - inventory_2022 
  11. watch     - 5   - inventory_2022 
  12. phone     - 25  - inventory_2022

I want to fill/ enter missing records from base(group) to all other groups

Expected output :

  1. tv        - 30  - base 
  2. microwave - 10  - base 
  3. watch     - 5   - base 
  4. phone     - 25  - base 
  5. washer    - 15  - base 
  6. dryer.    - 14  - base 
  7. tv        - 30  - inventory_2021 
  8. microwave - 7   - inventory_2021 
  9. watch     - 5   - inventory_2021 
  10. phone     - 16  - inventory_2021 
  11. washer    - 15  - inventory_2021 
  12. dryer.    - 14  - inventory_2021 
  13. tv        - 30  - inventory_2022 
  14. microwave - 10  - inventory_2022 
  15. watch     - 5   - inventory_2022 
  16. phone     - 25  - inventory_2022 
  17. washer    - 15  - inventory_2022 
  18. dryer.    - 14  - inventory_2022

So in the output I have same number of entries in each group and total number of entries should match the base group.

答案1

得分: 0

使用CROSS JOIN来创建所有缺失的行。然后，您可以与原始表格进行LEFT JOIN以填补空缺。

  1. WITH all_prods_and_groups AS (
  2.     SELECT p1.prod_name, p1.quantity, p2.group
  3.     FROM products AS p1
  4.     CROSS JOIN (
  5.         SELECT DISTINCT `group`
  6.         FROM products
  7.     ) AS p2
  8.     WHERE p1.group = 'base'
  9. )
  10. SELECT t1.prod_name, 
  11.     IFNULL(t2.quantity, t1.quantity) AS quantity, 
  12.     IFNULL(t2.group, t1.group) AS `group`
  13. FROM all_prods_and_groups AS t1
  14. LEFT JOIN products AS t2 ON t1.prod_name = t2.prod_name AND t1.group = t2.group

演示链接

英文:

Use CROSS JOIN to create all the missing rows. Then you can LEFT JOIN this with the original table to fill in the gaps.

  1. WITH all_prods_and_groups AS (
  2.     SELECT p1.prod_name, p1.quantity, p2.group
  3.     FROM products AS p1
  4.     CROSS JOIN (
  5.         SELECT DISTINCT `group`
  6.         FROM products
  7.     ) AS p2
  8.     WHERE p1.group = 'base'
  9. )
  10. SELECT t1.prod_name, 
  11.     IFNULL(t2.quantity, t1.quantity) AS quantity, 
  12.     IFNULL(t2.group, t1.group) AS `group`
  13. FROM all_prods_and_groups AS t1
  14. LEFT JOIN products AS t2 ON t1.prod_name = t2.prod_name AND t1.group = t2.group

DEMO

答案2

得分: 0

MySQL和Presto/Trino是两种稍微不同的SQL方言。基本思想是相同的 - 构建产品和组的“基本”笛卡尔积。如果您确信所有产品都存在于基本表中，您可以简单地使用以下Presto/Trino查询：

  1. -- 示例数据
  2. WITH dataset(prod_name, quantity, "group") as (
  3.     values ('tv'       , 30, 'base'),
  4.         ('microwave', 10, 'base'),
  5.         ('watch'    , 5 , 'base'),
  6.         ('phone'    , 25, 'base'),
  7.         ('washer'   , 15, 'base'),
  8.         ('dryer'   , 14, 'base'),
  9.         ('microwave', 7 , 'inventory_2021'),
  10.         ('phone'    , 16, 'inventory_2021'),
  11.         ('tv'       , 30, 'inventory_2022'),
  12.         ('watch'    , 5 , 'inventory_2022'),
  13.         ('phone'    , 25, 'inventory_2022')
  14. ),
  15. -- 查询部分
  16. base_products AS (
  17.     SELECT prod_name, quantity
  18.     FROM dataset AS p1
  19.     WHERE "group" = 'base' -- 选择所有基本产品
  20. ),
  21. all_groups as (
  22.     SELECT distinct "group" grp
  23.     FROM dataset
  24. ),
  25. base_all as(
  26.     SELECT b.prod_name, b.quantity, grp -- 构建基本查找表
  27.     FROM base_products b
  28.     CROSS JOIN all_groups
  29. )
  31. select b.prod_name,
  32.     coalesce(d.quantity, b.quantity) quantity, -- 使用“原始”数量
  33.     b.grp "group"
  34. from base_all b
  35. left join dataset d on b.prod_name = d.prod_name and b.grp = d."group"
  36. ;

输出：

prod_name quantity group
tv 30 inventory_2021
microwave 7 inventory_2021
watch 5 inventory_2021
phone 16 inventory_2021
washer 15 inventory_2021
dryer 14 inventory_2021
tv 30 base
microwave 10 base
watch 5 base
phone 25 base
washer 15 base
dryer 14 base
tv 30 inventory_2022
microwave 10 inventory_2022
watch 5 inventory_2022
phone 25 inventory_2022
washer 15 inventory_2022
dryer 14 inventory_2022
英文:

MySQL and Presto/Trino are two a bit SQL different dialects. The basic idea is the same - build the "base" cartesian product of products and groups. If you are sure that all products are present in base you can go simply with the following for Presto/Trino:

  1. -- sample data
  2. WITH dataset(prod_name, quantity, "group") as (
  3.     values ('tv'       , 30, 'base'),
  4.         ('microwave', 10, 'base'),
  5.         ('watch'    , 5 , 'base'),
  6.         ('phone'    , 25, 'base'),
  7.         ('washer'   , 15, 'base'),
  8.         ('dryer'   , 14, 'base'),
  9.         ('microwave', 7 , 'inventory_2021'),
  10.         ('phone'    , 16, 'inventory_2021'),
  11.         ('tv'       , 30, 'inventory_2022'),
  12.         ('watch'    , 5 , 'inventory_2022'),
  13.         ('phone'    , 25, 'inventory_2022')
  14. ),
  15. -- query parts
  16. base_products AS (
  17.     SELECT prod_name, quantity
  18.     FROM dataset AS p1
  19.     WHERE "group" = 'base' -- select all base products
  20. ),
  21. all_groups as (
  22.     SELECT distinct "group" grp
  23.     FROM dataset
  24. ),
  25. base_all as(
  26.     SELECT b.prod_name, b.quantity, grp -- build base lookup table
  27.     FROM base_products b
  28.     CROSS JOIN all_groups
  29. )
  31. select b.prod_name,
  32.     coalesce(d.quantity, b.quantity) quantity, -- use "original" quantity
  33.     b.grp "group"
  34. from base_all b
  35. left join dataset d on b.prod_name = d.prod_name and b.grp = d."group"
  36. ;

Output:

prod_name quantity group
tv 30 inventory_2021
microwave 7 inventory_2021
watch 5 inventory_2021
phone 16 inventory_2021
washer 15 inventory_2021
dryer 14 inventory_2021
tv 30 base
microwave 10 base
watch 5 base
phone 25 base
washer 15 base
dryer 14 base
tv 30 inventory_2022
microwave 10 inventory_2022
watch 5 inventory_2022
phone 25 inventory_2022
washer 15 inventory_2022
dryer 14 inventory_2022

huangapple
  • 本文由 发表于 2023年7月7日 08:08:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/76633187.html
  • database
  • mysql
  • presto
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定