sql объявить select calcuation в качестве переменной для дальнейшего использования в запросе

Я хочу изменить конкретное вычисление внутри запроса select на переменную, чтобы повторно обработать результат для других вычислений внутри запроса. В этом примере мне нужно значение в качестве переменной dt_raw. До сих пор мне не удалось реализовать решение.

SELECT
cpe.entity_id AS product_id,
cpe.sku,
CASE
    WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 3  /* on stock burgsdorgstrasse */
        THEN 3
    WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 4  /* on stock hoppegarten */
        THEN 7
    WHEN sum(sm_qty) > 2 AND csi.stock_id = 3                       /* history burgsdorfstrasse */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 3)
    WHEN sum(sm_qty) > 2 AND csi.stock_id = 4                       /* history hoppegarten */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 4)
        ELSE cped.value                                             /* default_delivery_time */
    END AS dt_raw


FROM `test_live`.`catalog_product_entity` cpe

LEFT JOIN `test_live`.`cataloginventory_stock_item` csi
ON cpe.entity_id = csi.product_id

LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped     /* default_delivery_time */
ON cpe.entity_id = cped.entity_id
AND cped.attribute_id = 392

LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped2    /* min_qty_delivery_time */
ON cpe.entity_id = cped2.entity_id
AND cped2.attribute_id = 393

LEFT JOIN `test_live`.`stock_movement` sm
ON cpe.entity_id = sm.sm_product_id
AND sm.sm_type = "supply"
AND sm.sm_date > NOW() - Interval 90 DAY

LEFT JOIN `test_live`.`purchase_order` po
ON po.po_num = sm.sm_po_num


WHERE
csi.is_favorite_warehouse = 1
AND (csi.stock_id = 3 OR csi.stock_id = 4)

GROUP BY cpe.entity_id

Я хочу использовать результаты dt_rawв дальнейших расчетах.

например:

concat ("ca. ", round(dt_raw), " weeks") as delivery_time

CASE
    WHEN dt_raw <= 30
    THEN 50
    ELSE 0
END AS amazon_qty

2 ответа

  1. Если вы хотите использовать результат запроса без изменений, сохраните его в таблице. После этого используйте select, чтобы получить нужные данные.

    СОЗДАВАТЬ

    CREATE TABLE your_schema.dt_raw_temp
    SELECT
    cpe.entity_id AS product_id,
    cpe.sku,
    CASE
        WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 3  /* on stock burgsdorgstrasse */
            THEN 3
        WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 4  /* on stock hoppegarten */
            THEN 7
        WHEN sum(sm_qty) > 2 AND csi.stock_id = 3                       /* history burgsdorfstrasse */
            THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 3)
        WHEN sum(sm_qty) > 2 AND csi.stock_id = 4                       /* history hoppegarten */
            THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 4)
            ELSE cped.value                                             /* default_delivery_time */
        END AS dt_raw
    
    
    FROM `test_live`.`catalog_product_entity` cpe
    
    LEFT JOIN `test_live`.`cataloginventory_stock_item` csi
    ON cpe.entity_id = csi.product_id
    
    LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped     /* default_delivery_time */
    ON cpe.entity_id = cped.entity_id
    AND cped.attribute_id = 392
    
    LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped2    /* min_qty_delivery_time */
    ON cpe.entity_id = cped2.entity_id
    AND cped2.attribute_id = 393
    
    LEFT JOIN `test_live`.`stock_movement` sm
    ON cpe.entity_id = sm.sm_product_id
    AND sm.sm_type = "supply"
    AND sm.sm_date > NOW() - Interval 90 DAY
    
    LEFT JOIN `test_live`.`purchase_order` po
    ON po.po_num = sm.sm_po_num
    
    
    WHERE
    csi.is_favorite_warehouse = 1
    AND (csi.stock_id = 3 OR csi.stock_id = 4)
    
    GROUP BY cpe.entity_id;
    

    ВЫБИРАТЬ

    SELECT CONCAT('ca. ', round(dt_raw), ' weeks') AS delivery_time, 
           IF(dt_raw <= 30, 50, 0) AS amazon_qty
    FROM your_schema.dt_raw_temp;
    

    Преимущество использования таблиц заключается в том, что вы можете воспроизвести
    вычисления в любой данный момент, пока вы держите свои таблицы.

  2. Просто выберите из запроса:

    select
      product_id,
      sku,
      dt_raw,
      concat ("ca. ", round(dt_raw), " weeks") as delivery_time,
      case when dt_raw <= 30 then 50 else 0 end as amazon_qty
    from
    (
      SELECT
        cpe.entity_id AS product_id,
        cpe.sku,
        CASE
          WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 3  /* on stock burgsdorgstrasse */
            THEN 3
          WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 4  /* on stock hoppegarten */
            THEN 7
          WHEN sum(sm.sm_qty) > 2 AND csi.stock_id = 3                                /* history burgsdorfstrasse */
            THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 3)
          WHEN sum(sm.sm_qty) > 2 AND csi.stock_id = 4                                /* history hoppegarten */
            THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 4)
            ELSE cped.value                                                           /* default_delivery_time */
        END AS dt_raw
      FROM test_live.catalog_product_entity cpe
      LEFT JOIN test_live.cataloginventory_stock_item csi ON cpe.entity_id = csi.product_id AND csi.is_favorite_warehouse = 1 AND csi.stock_id in (3,4)
      LEFT JOIN test_live.catalog_product_entity_decimal cped     /* default_delivery_time */ ON cpe.entity_id = cped.entity_id AND cped.attribute_id = 392
      LEFT JOIN test_live.catalog_product_entity_decimal cped2    /* min_qty_delivery_time */ ON cpe.entity_id = cped2.entity_id AND cped2.attribute_id = 393
      LEFT JOIN test_live.stock_movement sm ON cpe.entity_id = sm.sm_product_id AND sm.sm_type = 'supply' AND sm.sm_date > NOW() - Interval 90 DAY
      LEFT JOIN test_live.purchase_order po ON po.po_num = sm.sm_po_num
      GROUP BY cpe.entity_id
    ) prod;