sql - 在 SQLite 中使用分区方式

Question

我正在尝试重新创建本文中使用的代码https://blog.getdbt.com/modeling-marketing-attribution/但主要区别在于我必须使用 SQLite。我无法弄清楚为什么我无法创建字段，这些字段将为我计算分配给每个会话的归因点数奠定基础。我不确定我是否正确使用了“分区依据”功能。

从文章翻译到我的数据是：

• 会话 = 展示次数
• Customer_ID = Onovative_ID

对此的任何帮助将不胜感激！

询问：

select
*,
count(*) over (
partition by on_id
) as total_sessions,

row_number() over (
partition by on_id
order by sessions.started_at
) as session_number

from
//step 2
(
select impressions.*, loans.opendt 
from (
select "Onovative Id" as on_id, impdt as impression_date, "Email" as medium , Template as type
from Emails
UNION
select "Onovative Id", impdt, Type, "CIQ"
from Outbound
UNION
select "CIQ ID", impdt, Channnel, "Channel Detail"
from ImageWorks
) as impressions
join Listing loans on loans."Onovative Id" = impressions.on_id
where 
impression_date <= opendt
and impression_date > date(opendt,'-30 day')
--order by on_id
) as two

工作查询：

select impressions.*, loans.opendt 
from (
select "Onovative Id" as on_id, impdt as impression_date, "Email" as medium , Template as type
from Emails
UNION
select "Onovative Id", impdt, Type, "CIQ"
from Outbound
UNION
select "CIQ ID", impdt, Channnel, "Channel Detail"
from ImageWorks
) as impressions
join Listing loans on loans."Onovative Id" = impressions.on_id
where 
impression_date <= opendt
and impression_date > date(opendt,'-30 day')

工作查询的输出：

on_id    impression_date              medium               type       opendt
10001    2020-07-02 00:00             Letter    ImageWorks Refi              2020-07-29 00:00
70001    2020-07-20 00:00             Postcard              ImageWorks SmartTrack               2020-07-29 00:00
03301    2020-08-25 11:57             Email     CIQ         2020-09-21 00:00
02201    2020-05-11 00:00             Postcard              ImageWorks SmartTrack               2020-06-01 00:00
50001    2020-09-16 10:21             Email     CIQ         2020-09-28 00:00
10001    2020-09-16 10:21             Email    product offer e-statements email             2020-09-28 00:00
00601    2020-09-19 09:30             Email     CIQ         2020-09-28 00:00
00901    2020-09-16 10:21             Email     CIQ         2020-10-05 00:00
00501    2020-09-16 10:21             Email    product offer e-statements email             2020-10-05 00:00
00101    2020-09-19 09:30             Email     CIQ         2020-10-05 00:00
00401    2020-10-01 09:42             Email     CIQ         2020-10-05 00:00
00801    2020-10-04 16:14             Email     CIQ         2020-10-05 00:00
00301    2020-07-18 11:11             Email     CIQ         2020-07-28 00:00
00012    2020-06-08 11:43             Direct Mail Large Postcard           CIQ         2020-07-03 00:00

**SYNTAX ERROR: (doesn’t give line number)**

``` [14:41:37] Error while executing SQL query on database 'save_pandas3': near "(": syntax error```

标签： sqlsqlitewindow-functionspartition-by