sql - Postgres : Unable to determine percent of successful events ending in a completed trip

Question

SQL 大师，我在练习 SQL 技能时试图解决这个具有挑战性的问题，但是我被卡住了，如果有人能提供帮助，我将不胜感激。

注册被定义为事件表中标记为“sign_up_success”的事件。对于每个城市(“A”和“B”)和一周中的每一天，确定 2016 年第一周在注册日期后 10 小时内完成旅行的注册百分比。

表名:trips

Column Name                           Datatype
id                                     integer
client_id                              integer (Foreign keyed to 
events.rider_id)
driver_id                              integer
city_id                                Integer (Foreign keyed to 
cities.city_id)
client_rating                          integer
driver_rating                          integer
request_at                             Timestamp with timezone
predicted_eta                          Integer
actual_eta                             Integer
status                                 Enum(‘completed’, 
‘cancelled_by_driver’, ‘cancelled_by_client’)

表名:城市

Column Name   Datatype
city_id        integer
city_name      string

表名:事件

Column Name     Datatype
device_id        integer
rider_id         integer
city_id          integer
event_name       Enum(‘sign_up_success’, ‘attempted_sign_up’, 
‘sign_up_failure’)
_ts              Timestamp with timezone

在这条线上尝试了一些东西，但是它离预期的答案还差得很远:

SELECT *
FROM trips AS trips
LEFT JOIN cities AS cities ON trips.city_id = cities.city_id
LEFT JOIN events AS events ON events.client_id = events.rider_id
WHERE events.event_name = "sign_up_success"
AND Convert(datetime, trips.request_at') <= Convert(datetime, '2016-01-
07' )
AND DATEDIFF(d, Convert(datetime, events._ts), Convert(datetime, 
trips.request_at)) < 7 days
AND events.status = "completed

期望的结果如下所示:

Monday  A   x%
Monday  B   y%
Tuesday  A   z% 
Tuesday  A   p%

有人可以帮忙吗

Answer 1

首先，我假设 "trips"."city_id"是强制性的，所以在连接城市时我使用 INNER JOIN 而不是 LEFT JOIN。

然后，要指定字符串常量，您需要使用单引号。查询中还有一些其他变化——希望您自己会注意到它们。

此外，查询可能会失败，因为我实际上并没有运行它(很遗憾，您没有提供样板 SQL)。

带有“周”第一个参数的 date_trunc() 函数根据您当前的时区设置(请参阅 https://www.postgresql.org/docs/current/static/functions-datetime.html)将您的时间戳转换为“相应周的第一天，时间 00:00:00”。

我对该值使用了 GROUP BY，分组的第二“层”是城市 ID。

另外，我在 count() 旁边使用了“filter (where ...)”——它只允许计算所需的行数。

最后，我使用 CTE 改进了查询的结构和可读性。

如果失败请告诉我，我会修复它。一般来说，这种方法必须有效。

with data as (
  select
    left(date_trunc('week', t.request_at)::text, 10) as period,
    c.city_id,
    count(distinct t.id) as trips_count,
    count(*) filter (
      where
        e.event_name = 'sign_up_success'
        and e._ts < t.request_at + interval '10 hour'
    ) as successes_count
  from trips as t
  join cities as c on t.city_id = c.city_id
  left join events as e on t.client_id = e.rider_id and e._ts
  where
    t.request_at between '2016-01-01' and '2016-01-08'
  group by 1, 2
)
select
  *,
  round(100 * success_count::numeric / trips_count, 2)::text || '%' as ratio_percent
from data
order by period, city_id
;