如何用AlaSQL在Retool中获取最新created_at对应的stock值

我正在使用Retool（该工具采用AlaSQL查询JSON数据），需要从包含多条数据的集合中，获取created_at日期最新的那条数据的stock字段值。

目前我已经写出以下两个查询，但不清楚如何进一步筛选以实现目标：

select tracked_products 
from {{ean.data}} 
where created_at=(select MAX(created_at) from {{ean.data}});

select tracked_products 
from {{ean.data}} 
where price < 40

解决方案 #

你可以通过两种方式直接获取目标stock值，同时支持添加额外筛选条件：

方法1：子查询匹配最新日期

直接指定查询stock字段，通过子查询找到最大的created_at并匹配：

SELECT stock
FROM {{ean.data}}
WHERE created_at = (SELECT MAX(created_at) FROM {{ean.data}});

方法2：排序后取第一条

利用AlaSQL支持的ORDER BY和LIMIT语法，按created_at降序排列后取第一条数据的stock：

SELECT stock
FROM {{ean.data}}
ORDER BY created_at DESC
LIMIT 1;

如果需要同时加入价格筛选（比如price < 40），可以把条件加到WHERE子句中：

-- 带价格筛选的子查询方式
SELECT stock
FROM {{ean.data}}
WHERE created_at = (SELECT MAX(created_at) FROM {{ean.data}} WHERE price < 40)
AND price < 40;

-- 带价格筛选的排序方式
SELECT stock
FROM {{ean.data}}
WHERE price < 40
ORDER BY created_at DESC
LIMIT 1;

查询结果示例数据 #

[
  {
    "id": 172165913,
    "offer_data_id": 2208536,
    "stock": 2,
    "sold": 0,
    "revenue": "0.00",
    "price": 39.99,
    "status": "success",
    "created_at": "2022-12-16T12:12:26.000000Z",
    "updated_at": "2022-12-16T12:12:26.000000Z"
  },
  {
    "id": 173409443,
    "offer_data_id": 2208536,
    "stock": 2,
    "sold": 0,
    "revenue": "0.00",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-17T03:15:58.000000Z",
    "updated_at": "2022-12-17T03:15:58.000000Z"
  },
  {
    "id": 174659591,
    "offer_data_id": 2208536,
    "stock": 2,
    "sold": 0,
    "revenue": "0.00",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-18T16:22:45.000000Z",
    "updated_at": "2022-12-18T16:22:45.000000Z"
  },
  {
    "id": 175895075,
    "offer_data_id": 2208536,
    "stock": 2,
    "sold": 0,
    "revenue": "0.00",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-19T03:18:53.000000Z",
    "updated_at": "2022-12-19T03:18:53.000000Z"
  },
  {
    "id": 177134025,
    "offer_data_id": 2208536,
    "stock": 2,
    "sold": 0,
    "revenue": "0.00",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-20T15:35:48.000000Z",
    "updated_at": "2022-12-20T15:35:48.000000Z"
  },
  {
    "id": 178391290,
    "offer_data_id": 2208536,
    "stock": 2,
    "sold": 0,
    "revenue": "0.00",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-21T03:09:22.000000Z",
    "updated_at": "2022-12-21T03:09:22.000000Z"
  },
  {
    "id": 179654380,
    "offer_data_id": 2208536,
    "stock": 1,
    "sold": 1,
    "revenue": "34.99",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-22T03:13:50.000000Z",
    "updated_at": "2022-12-22T03:13:50.000000Z"
  },
  {
    "id": 180918092,
    "offer_data_id": 2208536,
    "stock": 1,
    "sold": 0,
    "revenue": "0.00",
    "price": 34.99,
    "status": "success",
    "created_at": "2022-12-23T03:19:42.000000Z",
    "updated_at": "2022-12-23T03:19:42.000000Z"
  }
]

内容的提问来源于stack exchange，提问作者SvanZ