获取何时在公式中工作的案例

Question

我在sql中有一个可以工作的自定义列：

,case
    when(extract(day from now() - 2)) < 3
      then '1'
    else extract(day from now() - 2)
  end as MTD

然后我想要有另一个列来处理这个查询，但是当我意识到如果不保存/命名它就不能对它进行查询时，我尝试在公式中获得case，认为它会以同样的方式工作

我接下来想要的原始查询应该是：

,(combo.autotradercom_vdp + combo.carscom_vdp) / min(MTD, combo.age) as VDP/MTD

所以我没有按照我的逻辑构建这个，它应该可以工作，但却不是：

,(combo.autotradercom_vdp + combo.carscom_vdp) / min(case when (extract(day from now () - 2)) < 3 then '1' else extract (day from now() - 2) end, combo.age) as VDP/MTD

我收到一个错误："#“处或附近的语法错误，我知道问题出在查询的min()部分，但似乎不能理解问题所在。

如有任何帮助，我们不胜感激！

编辑：

添加了整个查询

--work in brogress, will automate inventory review excel doc
select
  case
    when combo.total_repairs < 1
      then 'None'
    when combo.total_repairs < (combo.guaranteed_price * 4) / 100
      then 'Lo'
    when combo.total_repairs between(combo.guaranteed_price * 4) / 100
    and (
      combo.guaranteed_price * 8
    )
    / 100
      then 'Med'
    else 'Hi'
  end as Repair_Tier
  , case
    when combo.list_price < combo.guaranteed_price
      then '0'
    else combo.seller_upside_percentage
  end as Seller_Upside
  , combo.days_to_expiration as Days_remaining_on_contract
  , case
    when(combo.deposit + combo.needs_repairs + combo.going_to_auction + combo.in_transit + combo.hold_for_trade_in + combo.hold_for_financing) > 0
    or left(combo.paperwork_missing, 3) = 'Yes'
      then concat('Reserved - ', case when combo.deposit > 0 then 'Deposit' when combo.needs_repairs > 0 then 'Needs Repairs' when combo.going_to_auction > 0 then 'Going to Auction' when combo.in_transit > 0 then 'In Transit' when combo.hold_for_trade_in > 0 then 'Hold for Trade-In' when combo.hold_for_financing > 0 then 'Hold for Financing' else concat('Paperwork Missing - ', combo.paperwork_missing_reason) end)
    else ''
  end as Reserved
  , case
    when combo.future_test_drives > 0
      then combo.future_test_drives
    else '0'
  end as Future_Test_Drives
  , case
    when combo.recent_test_drives > 0
      then combo.recent_test_drives
    else '0'
  end as Recent_Test_Drivescase
  , case
    when combo.recent_buyer_leads > 0
      then combo.recent_buyer_leads
    else '0'
  end as Recent_Buyer_Leads
  --MTD-2
  , case
    when(extract(day from now() - 2)) < 3
      then 1
    else extract(day from now() - 2)
  end as MTD 
  , (combo.autotradercom_vdp + combo.carscom_vdp) / min (case
    when(extract(day from now() - 2)) < 3
      then '1'
    else extract(day from now() - 2)
  end, combo.age) as VDP/MTD
from
  [combo]

Answer 1

有两个问题：

, (combo.autotradercom_vdp + combo.carscom_vdp) / min (case
    when(extract(day from now() - 2)) < 3
      then '1' -- why a string instead of an INT?
    else extract(day from now() - 2)
  end, combo.age) as VDP/MTD
                  #2:^^^^^^^ this alias will result in an error, too, must be quoted
     ^^^^^^^^^^^ #1: where is this coming from?

这应该是可行的：

, (combo.autotradercom_vdp + combo.carscom_vdp) / min (case
    when(extract(day from now() - 2)) < 3
      then 1
    else extract(day from now() - 2)
  end) as "VDP/MTD"

Answer 2

我解决了这个问题，使用数学而不是sql公式从两个最小值中获取最小值，因为最小值只能引用列

解决方案：

, cast(
    (combo.autotradercom_vdp + combo.carscom_vdp) / (0.5 * (((case when(extract(day from now() - 2)) < 3 then 1 else extract(day from now() - 2) end) + (
            case
              when combo.age > 0
                then combo.age
              else 1
            end
          )
        )
        - abs(
          (
            case
              when(extract(day from now() - 2)) < 3
                then 1
              else extract(day from now() - 2)
            end
          )
          - (
            case
              when combo.age > 0
                then combo.age
              else 1
            end
          )
        )
      )
    )
    as int
  )
  as "VDP/DL"