postgresql - SQLAlchemy 核心 : Creating a last_value window function for postgresql

Question

我正在尝试使用 SQLAlchemy Core 创建以下 PostgreSQL 查询:

SELECT DISTINCT ON (carrier) carrier,
       LAST_VALUE(ground) OVER wnd AS ground,
       LAST_VALUE(destinationzipstart) OVER wnd AS destinationzipstart,
       LAST_VALUE(destinationzipend) OVER wnd AS destionationzipend
FROM   tblshippingzone
WHERE  sourcezipstart <= 43234
AND    sourcezipend >= 43234
AND    destinationzipstart NOT BETWEEN 99500 AND 99950
AND    destinationzipstart NOT BETWEEN 96700 AND 96899
AND    destinationzipstart >= 1000
AND    (contiguous IS NULL OR contiguous = True)
AND    ground IS NOT NULL
WINDOW wnd AS (
   PARTITION BY carrier ORDER BY ground DESC, destinationzipstart);

这是我目前所拥有的:

# Short-hand for accessing cols
all_cols = ShippingZoneDAL._table.c

# Window params
window_p = {'partition_by': all_cols.carrier,
            'order_by': [desc(all_cols.ground), all_cols.destination_zip_start]}

# Select columns
select_cols = [distinct(all_cols.carrier).label('carrier'),
           over(func.last_value(all_cols.ground), **window_p).label('ground'),
           over(func.last_value(all_cols.destination_zip_start), **window_p).label('destination_zip_start'),
           over(func.last_value(all_cols.destination_zip_end), **window_p).label('destination_zip_end')]

# Filter exprs
exprs = [all_cols.source_zip_start <= 43234,
     all_cols.source_zip_end >= 43234,
     ~all_cols.destination_zip_start.between(99500, 99950),  # Alaska zip codes
     ~all_cols.destination_zip_start.between(96700, 96899),  # Hawaii zip codes
     all_cols.destination_zip_start >= 1000,                 # Eliminates unusual territories
     or_(all_cols.contiguous == True, all_cols.contiguous == None),
     all_cols.ground != None]

# Build query
query = select(*select_cols).where(and_(*exprs))

但是在构建查询时出现错误:

ArgumentError: FROM expression expected

知道我在这里遗漏了什么吗？

奖励积分:

我原本希望窗口函数是这样的:

WINDOW wnd AS (
  PARTITION BY carrier ORDER BY ground
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

但根据此支持请求，sqlalchemy 似乎不支持“无限制的前导和无限制的跟随之间的行”: https://bitbucket.org/zzzeek/sqlalchemy/issue/3049/support-range-specificaiton-in-window

是否可以使用该子句？

Answer 1

这主要只是将各种方法重新安排到工作顺序中的问题。这是答案，如果有人遇到类似的事情:

# Short-hand for accessing cols
all_cols = ShippingZoneDAL._table.c

# Window params
window_p = {'partition_by': all_cols.carrier,
            'order_by': [desc(desc(all_cols.ground)), all_cols.destination_zip_start]}

# Select columns
select_cols = select(
                [all_cols.carrier,
                 func.last_value(all_cols.ground).over(**window_p).label(shipment_method),
                 func.last_value(all_cols.destination_zip_start).over(**window_p).label('destination_zip_start'),
                 func.last_value(all_cols.destination_zip_end).over(**window_p).label('destination_zip_end')])

# Filter exprs
exprs = [all_cols.source_zip_start <= 43234,
         all_cols.source_zip_end >= 43234,
         ~all_cols.destination_zip_start.between(99500, 99950),
         ~all_cols.destination_zip_start.between(96700, 96899),
         all_cols.destination_zip_start >= 1000,
         or_(all_cols.contiguous == True, all_cols.contiguous == None),
         all_cols.ground != None]

# Build query
query = select_cols.where(and_(*exprs)).distinct(all_cols.carrier)

上述解决方案要牢记的关键注意事项:

• 在这种情况下，SQLAlchemy Core 不会将 select(*select_cols) 视为等同于 select([all_cols.ground, etc])。可能是因为 over 方法需要在选择的上下文中计算，或者您丢失了对 FROM 表的引用。

• 要在 PostgreSQL 中使用 DISTINCT ON，请确保 distinct 位于主选择之后。如果仅在 SELECT 本身中使用，它将成为该列的标准 DISTINCT 子句。

• 注意标签本身 - 返回的列将只定义 key，而不像对象中的普通表列那样定义 name。

如果有人仍然想解决我的奖励问题，请随时 :) 仍然不确定是否有办法在 SQLAlchemy 中使用它。