sql - SQLite-将子查询与LIMIT和错误“找不到表”一起使用

Question

我正在尝试计算2列之间的时间差的中位数。我找到了解决方法here
但是，当我将其合并到代码中时，出现以下错误：


没有这样的表：中位数值：SELECT timeToPost。


例如内部表的数量（用户最多）：

id      userJoinDate    firstPost       timeToPost
---------------------------------------------------
3666    1/4/2015 10:48  1/4/2015 11:48    0.04
3669    1/13/2015 8:05  1/13/2015 9:05    0.04
3672    1/13/2015 8:27  1/13/2015 9:27    0.04
3675    1/13/2015 9:27  1/13/2015 10:27   0.04
3678    1/13/2015 11:02 1/13/2015 12:02   0.04

码：

SELECT timeToPost

FROM(
SELECT up.id, userJoinDate, firstPost, round(julianday(firstPost)-julianday(userJoinDate),2) as timeToPost
FROM (SELECT u.id, u.create_date as userJoinDate, min(p.create_date) as firstPost 
FROM Users u
JOIN posts p
ON p.user_id = u.id
WHERE u.create_date > '2015-01-01'
GROUP BY 1
) as up

ORDER BY 4 DESC
) as medianVal

LIMIT 2 - (SELECT COUNT(*) FROM medianVal) % 2 -- odd 1, even 2
      OFFSET (SELECT (COUNT(*) - 1) / 2 FROM medianVal)

Answer 1

表别名不同于表本身。它不能在from子句中重复使用。

但是，CTE可以做您想要的事情，因此只需将逻辑重写为：

with medianval as (
      select up.id, userJoinDate, firstPost, round(julianday(firstPost)-julianday(userJoinDate),2) as timeToPost
      from (select u.id, u.create_date as userJoinDate, min(p.create_date) as firstPost 
            from Users u join
                 posts p
                 on p.user_id = u.id
            where u.create_date > '2015-01-01'
            group by 1
           ) up
      )
select timeToPost
from medianval
order by timeToPost desc
limit 2 - (select count(*) from medianVal) % 2 -- odd 1, even 2
offset (select (count(*) - 1) / 2 from medianVal)

注意：这仅解决缺少表的问题。我尚未验证代码是否存在其他问题或是否适合计算中位数。