sql - SQL的年度同类群组分析

我试图做一个队列分析-试图计算保留率。这是我的代码：

SELECT
  renter_id,
  min(DATE_PART('year', created_at)) AS first_rental_year,
  DATE_PART('year', created_ay) AS y1,
  round(100 * count(distinct b2.renter_id) /
    count(distinct b1.renter_id)) AS retention
FROM bookings AS b1
  LEFT JOIN bookings AS b2 ON
    b1.renter_id = b2.renter_id
    AND DATE_PART('year', b1.created_at) = DATE_PART(datetime('year', b2.created_at, '-1 year'))
GROUP BY  1
ORDER BY 2;

但它根本不起作用。。。The error message I get says: Hint: No function matches the given name and argument types. You might need to add explicit type casts.
如果能给你一些建议，那就太棒了。

最佳答案

紧要的问题是在PostgreSQL中不存在的datetime()函数。还有一些别名和聚合的问题。这会让你更接近你想去的地方：

SELECT
  b1.renter_id,
  min(DATE_PART('year', b1.created_at)) AS first_rental_year,
              -- Needs an aggregate
  round(100. * count(distinct b2.renter_id) /     -- Use float, see 100.
    count(distinct b1.renter_id)) AS retention
FROM bookings AS b1
LEFT JOIN bookings AS b2
  ON  b1.renter_id = b2.renter_id
  AND extract(year, b1.created_at) + 1 = extract(year, b2.created_at)
GROUP BY 1
ORDER BY 2;

然而，你的群组功能逻辑似乎有缺陷。您应该将问题编辑为：
更详细地解释您希望如何计算保留金
添加表结构和一些示例数据
显示预期结果。

关于sql - SQL的年度同类群组分析，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37403356/