mysql 使用日期范围的最大和最小子查询

Question

我有以下查询:

SELECT
(Date + INTERVAL -(WEEKDAY(Date)) DAY) `Date`,

我想在此处使用子查询从最大和最小日期获取最旧和最新的库存:

(从 clabDevelopment.fba_history_daily 中选择可销售，其中 Date =
最大(日期))

max(Date), min(Date),
ASIN, 
ItemSKU, 
it.avgInv,
kt.Account, kt.Country, SUM(Sessions) `Sessions`, avg(Session_Pct)`Session_Pct`, 
sum(Page_Views)`Page_Views`,  avg(Page_Views_Pct)`Page_Views_Pct`,  avg(Buy_Box_Pct)`Buy_Box_Pct`, 
sum(Units_Ordered)`Units_Ordered`, sum(Units_Ordered_B2B) `Units_Ordered_B2B`,  
avg(Unit_Session_Pct)`Unit_Session_Pct`, avg(Unit_Session_Pct_B2B)`Unit_Session_Pct_B2B`,
sum(Ordered_Product_Sales)`Ordered_Product_Sales`, sum(Total_Order_Items) `Total_Order_Items`, sum(Actual_Sales) `Actual_Sales`,  
 sum(Orders) `Orders`, sum(PPC_Revenue) `PPC_Revenue`, sum(PPC_Orders) `PPC_Orders`,
sum(Revenue)`Revenue`,  sum(Sales_Tax_Collected) `Sales_Tax_Collected`,  sum(Total_Ad_Spend) `Total_Ad_Spend`, sum(Impressions) `Impressions`, 
 sum(Profit_after_Fees_before_Costs) `Profit_after_Fees_before_Cost`
FROM clabDevelopment.KPI_kpireport as kt

left outer join 
(SELECT Month(Date) as mnth, sku, account, country, avg(sellable)`avgInv` FROM clabDevelopment.`fba_history_daily`
where sellable  >= 0
group by Month(Date), sku, account, country) as it
on kt.ItemSKU = it.SKU
and kt.Account = it.account
and kt.Country = it.country
and it.mnth = Month(kt.Date)
WHERE kt.Country = 'USA' or kt.Country = 'CAN'
GROUP BY Account, Country,(Date + INTERVAL -(WEEKDAY(Date)) DAY), ItemSKU
ORDER BY Date desc

子查询将来 self 在底部加入的同一个表，除非我在那里按月分组。所以我想运行这个子查询并获取 max(Date) 日期的可销售下的值:

(select sellable from clabDevelopment.`fba_history_daily where Date = max(Date))

当我这样做时，我无法使用组功能。

Answer 1

如果不知道您的架构和引擎/数据库，就很难理解问题。但是，这是使用以下模式的最佳猜测:

fba_history_daily
- mnth
- sku
- account
- country
- sellable
- SKU

KPI_kpireport
- Account
- Country
- ItemSKU
- Account
- Date
- Country
- ASIN

以下查询将为您提供您要查找的内容。这使用 GROUP_CONCAT 来通过聚合构建所需的结果。使用嵌套查询连接，MySQL 可能会在内存中构建一个临时表来对那些不是最佳的记录进行排序。您可以使用 EXPLAIN 检查这一点，并且您会在详细信息中看到 Using tempor 。

SELECT
  (Date + INTERVAL -(WEEKDAY(Date)) DAY) `Date`,
  ASIN, 
  ItemSKU, 

  -- MIN
  (SUBSTRING_INDEX(GROUP_CONCAT(it.sellable ORDER BY it.Date ASC),',', 1) AS minSellable),

  -- MAX
  (SUBSTRING_INDEX(GROUP_CONCAT(it.sellable ORDER BY it.Date DESC),',', 1) AS maxSellable),

  -- AVG
  AVG(it.sellable) avgInv,

  kt.Account, kt.Country, SUM(Sessions) `Sessions`, avg(Session_Pct)`Session_Pct`, 
  sum(Page_Views)`Page_Views`,  avg(Page_Views_Pct)`Page_Views_Pct`,  avg(Buy_Box_Pct)`Buy_Box_Pct`, 
  sum(Units_Ordered)`Units_Ordered`, sum(Units_Ordered_B2B) `Units_Ordered_B2B`,  
  avg(Unit_Session_Pct)`Unit_Session_Pct`, avg(Unit_Session_Pct_B2B)`Unit_Session_Pct_B2B`,
  sum(Ordered_Product_Sales)`Ordered_Product_Sales`, sum(Total_Order_Items) `Total_Order_Items`, sum(Actual_Sales) `Actual_Sales`,  
  sum(Orders) `Orders`, sum(PPC_Revenue) `PPC_Revenue`, sum(PPC_Orders) `PPC_Orders`,
  sum(Revenue)`Revenue`,  sum(Sales_Tax_Collected) `Sales_Tax_Collected`,  sum(Total_Ad_Spend) `Total_Ad_Spend`, sum(Impressions) `Impressions`, 
  sum(Profit_after_Fees_before_Costs) `Profit_after_Fees_before_Cost`

FROM KPI_kpireport as kt

left outer join fba_history_daily it on 
  kt.ItemSKU = it.SKU
  and kt.Account = it.account
  and kt.Country = it.country
  and Month(it.Date) = Month(kt.Date)
  and it.sellable  >= 0

WHERE kt.Country = 'USA' or kt.Country = 'CAN'
GROUP BY Account, Country,(Date + INTERVAL -(WEEKDAY(Date)) DAY), ItemSKU
ORDER BY Date desc