sql - SQLite:当它是一个单独的查询时,部分代码就像一个魅力,但当它是一个更大的查询的一部分时不执行
问题描述
我对 SQLite 有点陌生,我遇到了一个我不知道如何解决的问题。
我有这个复杂的查询,其中一部分落后于 user_id 列并创建了一个 lag_id 列。如果两个条目不同,则 new_user 列中的条目应为 1,否则为 0。
这是我的代码(我正在使用 redash),它工作(编译并正确执行)与更大的查询相比,目标是找到用户已完成操作 1、2 和 3 的所有用户会话。
现在,当我将此查询用作更大查询的一部分时,它不会显示任何语法错误,但 lag_id 列与 user_id 相同,并且 new_user 始终为 0。请帮我调试一下,我不知道是什么我做错了,谢谢!
SELECT *,
CASE WHEN (user_id = LAG_ID) THEN 0 ELSE 1 END AS NewUser
FROM
(SELECT *,
(lag(user_id, 1 , 0) over (order by user_id DESC, created_at ASC)) AS LAG_ID
From query_655376)```
Here's the bigger query:
```select * from(
select * from(
select *,
case when (B_Visited > 0) AND C then 1 else 0 end AS C_Success from(
select *
, sum(B_Success) over (Partition BY SessionID order by user_id DESC, created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS B_Visited
from
(
select *,
case when (A_Visited > 0) AND B then 1 else 0 end AS B_Success from(
select *, A as A_Success
, sum(A) over (Partition BY SessionID order by user_id DESC, created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS A_Visited
from
(
select *, sum(NewSession) over (order by user_id DESC, created_at ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as SessionID from
(
select *, case when (Date_Diff >= 60 ) or NewUser then 1 ELSE 0 END AS NewSession from
(
select user_id, created_at, lag_id,
CASE action WHEN 'lms_practice' THEN 1 ELSE 0 END as A,
CASE action WHEN 'lms_view_step_content' THEN 1 ELSE 0 END as B,
CASE action WHEN 'lms_fin_cur_step_content' THEN 1 ELSE 0 END as C,
(strftime('%s',created_at)-strftime('%s',lag(created_at) over (order by user_id DESC, created_at ASC)))/60 as Date_Diff,
CASE WHEN (user_id = LAG_ID) THEN 0 ELSE 1 END AS NewUser
FROM
(SELECT *,
lag(user_id, 1 , 0) over (order by user_id DESC, created_at ASC) AS lag_id
From query_655376)
order by user_id DESC, created_at ASC
)
)
)
)))
)
where C_Success > 0
order by user_id DESC, created_at ASC);```
解决方案
我推测您希望以前的用户 id 仅基于created_at
. 这将表明:
(SELECT q.*,
lag(user_id) over (order by created_at ASC)) AS LAG_ID
FROM query_655376 q
)
请注意,这user_id DESC,
从order by
.
注意区别:
User_id Created_At Yours Mine
1 1 NULL NULL
2 2 1 1
1 3 1 2
1 4 1 1
3 5 2 1
推荐阅读
- laravel - 如何在 Laravel 迁移中创建连接表?
- swift - 如何合并 2 个带有淡入淡出过渡的视频?
- javascript - 为什么我在 javascript 中得到奇怪的日期?
- c++ - 使用模板时可能是编译器中的 SFINAE BUG?
- asp.net-core - 在 IdentityServer4 和 ASP.NET Core 中检索客户端名称
- php - 如何将此响应xml转换为json
- python - 创建新类实例的替代方法
- awk - 如果它们在文件中,如何替换字符串
- python - 更新后pip3“TypeError:'module'对象不可调用”
- python - 如何在熊猫中按特定行名称/值删除一行