SELECT *
FROM OUTPUT
WHERE CAST(TIME AS TIMESTAMP) >= CURRENT_DATE - 7 DAY;
SELECT o.*
FROM OUTPUT o
JOIN PRODUCT p ON p.ID = o.PRODUCTID -- 如果你表里叫 PRODUCT，就写 o.PRODUCT
WHERE UPPER(TRIM(p.CATEGORY)) IN (
'BIERE','MOLSON','LABATT','SLEEMEN','SLEEMENT','POPERS'
)
ORDER BY CAST(o.TIME AS TIMESTAMP) DESC;
SELECT
o.ID,
CAST(o.TIME AS TIMESTAMP) AS TS,
p.CATEGORY,
p.BRAND,
p.SUBJECT AS PRODUCT_NAME,
p.CODE,
p.UNIT,
o.AMOUNT,
o.TOTALPRICE
FROM OUTPUT o
JOIN PRODUCT p
ON p.ID = o.PRODUCTID -- 关键修正：用 PRODUCTID
WHERE
UPPER(TRIM(p.CATEGORY)) IN ('BIERE','MOLSON','LABATT','SLEEMEN','SLEEMENT','POPERS')
AND CAST(o.TIME AS TIMESTAMP) >= CURRENT_TIMESTAMP - 7 DAY
ORDER BY TS DESC, o.ID DESC;
SELECT
UPPER(TRIM(p.CATEGORY)) AS category,
UPPER(TRIM(p.BRAND)) AS brand,
TRIM(p.SUBJECT) AS product_name,
SUM(o.AMOUNT) AS qty, -- 销量
SUM(o.TOTALPRICE) AS revenue -- 销售额（可选）
FROM OUTPUT o
JOIN PRODUCT p
ON p.ID = o.PRODUCTID
WHERE
UPPER(TRIM(p.CATEGORY)) IN ('BIERE','MOLSON','LABATT','SLEEMEN','SLEEMENT','POPERS')
AND CAST(o.TIME AS TIMESTAMP) >= CURRENT_TIMESTAMP - 7 DAY
GROUP BY UPPER(TRIM(p.CATEGORY)), UPPER(TRIM(p.BRAND)), TRIM(p.SUBJECT)
ORDER BY qty DESC;
SELECT
UPPER(TRIM(p.CATEGORY)) AS category,
UPPER(TRIM(p.BRAND)) AS brand,
TRIM(p.SUBJECT) AS product_name,
SUM(o.AMOUNT) AS qty, -- 销量
SUM(o.TOTALPRICE) AS revenue -- 销售额（可选）
FROM OUTPUT o
JOIN PRODUCT p
ON p.ID = o.PRODUCTID
WHERE
UPPER(TRIM(p.CATEGORY)) IN ('TABAC','EMPIRE','BENSON','JTI MD')
AND CAST(o.TIME AS TIMESTAMP) >= CURRENT_TIMESTAMP - 7 DAY
GROUP BY UPPER(TRIM(p.CATEGORY)), UPPER(TRIM(p.BRAND)), TRIM(p.SUBJECT)
ORDER BY qty DESC;
SELECT
UPPER(TRIM(p.CATEGORY)) AS category,
UPPER(TRIM(p.BRAND)) AS brand,
TRIM(p.SUBJECT) AS product_name,
p.CODE,
p.UNIT,
MAX(p.STOCK) AS stock_current, -- 当前库存（来自 PRODUCT）
SUM(o.AMOUNT) AS qty_7d, -- 7天销量
SUM(o.TOTALPRICE) AS revenue_7d,
CASE
WHEN SUM(o.AMOUNT) = 0 THEN NULL
ELSE CAST(MAX(p.STOCK) AS DECIMAL(18,2)) / (SUM(o.AMOUNT) / 7.0)
END AS days_of_cover -- 以近7天均速估算还能卖几天（可选）
FROM OUTPUT o
JOIN PRODUCT p
ON p.ID = o.PRODUCTID
WHERE
UPPER(TRIM(p.CATEGORY)) IN ('BIERE','MOLSON','LABATT','SLEEMEN','SLEEMENT','POPERS')
AND CAST(o.TIME AS TIMESTAMP) >= CURRENT_TIMESTAMP - 7 DAY
GROUP BY
UPPER(TRIM(p.CATEGORY)),
UPPER(TRIM(p.BRAND)),
TRIM(p.SUBJECT),
p.CODE,
p.UNIT
ORDER BY qty_7d DESC;
SELECT
UPPER(TRIM(p.CATEGORY)) AS category,
TRIM(p.SUBJECT) AS product_name,
SUM(o.AMOUNT) AS qty_7d,
COALESCE(MAX(p.STOCK), 0) AS stock_current,
SUM(o.TOTALPRICE) AS revenue_7d,
-- 近7天日均销量：SUM/7；覆盖天数：库存 / 日均
CASE
WHEN SUM(o.AMOUNT) > 0
THEN CAST(COALESCE(MAX(p.STOCK),0) AS DECIMAL(18,2)) * 7.0 / SUM(o.AMOUNT)
ELSE NULL
END AS days_of_cover
FROM OUTPUT o
JOIN PRODUCT p
ON p.ID = o.PRODUCTID
WHERE
UPPER(TRIM(p.CATEGORY)) IN ('TABAC','EMPIRE','BENSON','JTI_MD') -- 你的集合
AND CAST(o.TIME AS TIMESTAMP) >= CURRENT_TIMESTAMP - 7 DAY
GROUP BY
UPPER(TRIM(p.CATEGORY)),
UPPER(TRIM(p.BRAND)),
TRIM(p.SUBJECT),
p.CODE,
p.UNIT
ORDER BY qty_7d DESC;