SQLazy 让复杂SQL开发少走90%弯路 写给每天和SQL死磕的你

只保留开票行（invoiced=1），每行 Amount 是自上次开票以来的累计值

WITH invoice_groups AS (
	SELECT id, dt, invoiced, amount,
    	SUM(invoiced) OVER (PARTITION BY id ORDER BY CASE WHEN id IS NULL THEN 1 ELSE 0 END, id ASC, dt DESC ROWS UNBOUNDED PRECEDING) AS grp
  	FROM invoice
),
aggregated AS (
  	SELECT id, grp, MAX(dt) AS dt, MAX(invoiced) AS invoiced, SUM(amount) AS amount
  	FROM invoice_groups
  	GROUP BY id, grp
)
SELECT id, dt, invoiced, amount
FROM aggregated
ORDER BY id, grp

需要根据前两列生成一个序列号 Seq：

• 按账户分组，按日期时间升序处理
• 如果当前行的活动时间与上一行的时间间隔超过 1 小时，则序列号重置为 1
• 否则（间隔 ≤ 1 小时），序列号递增 1

WITH t1 AS (
  SELECT account_number, dt FROM numEvents
),
t2 AS (
  SELECT account_number, dt,
    1 + SUM(CASE WHEN (col__5 + INTERVAL 3600 SECOND <= dt) THEN 1 ELSE 0 END)
      OVER (PARTITION BY account_number ORDER BY account_number ASC, dt ASC) AS grp
  FROM (
    SELECT t1.*,
      LAG(dt) OVER (PARTITION BY account_number ORDER BY account_number ASC, dt ASC) AS col__5
    FROM t1
  ) sub__6
)
SELECT
  ROW_NUMBER() OVER (PARTITION BY account_number, grp ORDER BY account_number ASC, dt ASC) AS seq,
  dt,
  account_number
FROM t2
ORDER BY account_number ASC, dt ASC

对数据表按ID和SPLIT字段分组后，根据组内日期是否重复来决定是否进一步按CUST分组，并汇总金额保留最近日期的记录。

WITH ranked AS (
  SELECT
    sub__13.*,
    DENSE_RANK() OVER (PARTITION BY ID, SPLIT ORDER BY DT DESC) AS rnk
  FROM splitAcc sub__13
),
with_totals AS (
  SELECT
    ID, SPLIT, CUST, DT, AMOUNT, rnk,
    CASE WHEN rnk = 1 THEN CUST ELSE NULL END AS rnk1,
    SUM(AMOUNT) OVER (PARTITION BY ID, SPLIT) AS total_amount,
    COUNT(DISTINCT CASE WHEN rnk = 1 THEN CUST ELSE NULL END) OVER (PARTITION BY ID, SPLIT) AS num_cust,
    SUM(AMOUNT) OVER (PARTITION BY ID, SPLIT, CUST) AS total_cust_amount
  FROM ranked
)
SELECT
  ID, SPLIT, CUST, DT,
  CASE WHEN num_cust = 1 THEN total_amount ELSE total_cust_amount END AS amt
FROM with_totals
WHERE rnk = 1

关联连个表，将发票金额按项目下的账户数平均分账，金额保留两位小数，最后一个账户分剩余的钱，保证总和不变。

WITH joined AS (
  SELECT p.id, p.projectid, p.accountcode, i.invoiceid, i.amount
  FROM projects p
  LEFT JOIN invoices i ON p.projectid = i.projectid
),
with_count AS (
  SELECT *,
    COUNT(invoiceid) OVER (PARTITION BY projectid) AS cnt
  FROM joined
),
with_split AS (
  SELECT *,
    ROUND(amount / cnt, 2) AS splitamount
  FROM with_count
),
with_sum AS (
  SELECT *,
    SUM(splitamount) OVER (PARTITION BY projectid) AS saa
  FROM with_split
),
adjusted AS (
  SELECT *,
    CASE
      WHEN ROW_NUMBER() OVER (PARTITION BY projectid ORDER BY accountcode ASC) = 1
      THEN ROUND(splitamount + amount - saa, 2)
      ELSE splitamount
    END AS f1
  FROM with_sum
)
SELECT invoiceid, amount, accountcode, projectid, f1 AS splitamount
FROM adjusted
ORDER BY invoiceid ASC, accountcode ASC