我的头在解决方案上旋转以下...我有一个工作查询，可以为我带来第一个create_date的行以及指定条件。现在，我想扩展该查询并加入另一个表事件，然后使用事件"注册"不同的所有记录。
我想回答我的查询的问题是，哪些用户具有带有RefererPath"/community％"的firsthit，依此类推，然后进一步过滤哪些则进行了"签名"。我在工作查询中解决的第一部分，但是如何重新定义它才能基于Ingeup操作返回用户？我在Postgres。
第一部分的工作查询：
SELECT DISTINCT ON (cookie_hash) cookie_hash, crdate, http_referer, refererpath
FROM trace t1
WHERE crdate = (SELECT MIN(crdate) FROM trace t2 WHERE t1.id = t2.id)
AND refererpath LIKE '/community%' AND http_referer IS NOT NULL AND http_referer NOT LIKE 'https://example.io%'
ORDER BY cookie_hash, crdate
我尝试的是：
SELECT DISTINCT ON (cookie_hash) t1.cookie_hash, t1.crdate, t1.http_referer, t1.refererpath
FROM trace t1
INNER JOIN event on t1.cookie_hash = event.cookie_hash
WHERE t1.crdate = (SELECT MIN(crdate) FROM trace t2 WHERE t1.id = t2.id)
AND refererpath LIKE '/community%' AND http_referer IS NOT NULL AND http_referer NOT LIKE 'https://example.io%'
AND event.action = 'signup'
ORDER BY cookie_hash, crdate
这是在我指定条件之前有一个注册事件的迹线的错误结果：/
我的两个表具有此结构：
表跟踪：
+--------------+-----------------------------+-----------------------------------------+
| Column | Type | Modifiers |
|--------------+-----------------------------+-----------------------------------------|
| id | integer | not null |
| cookie_hash | character varying(255) | default NULL::character varying |
| crdate | timestamp(0) with time zone | default NULL::timestamp with time zone |
| action | character varying(255) | default NULL::character varying |
| account_uuid | character varying(255) | default NULL::character varying |
+--------------+-----------------------------+-----------------------------------------+
和表事件：
+-----------------+-----------------------------+-----------------------------------------+
| Column | Type | Modifiers |
|-----------------+-----------------------------+-----------------------------------------|
| id | integer | not null |
| cookie_hash | character varying(255) | default NULL::character varying |
| crdate | timestamp(0) with time zone | default NULL::timestamp with time zone |
| remote_addr | character varying(255) | default NULL::character varying |
| refererhost | text | |
| refererpath | text | |
| http_referer | text | |
| account_uuid | character varying(255) | default NULL::character varying |
+-----------------+-----------------------------+-----------------------------------------+
这是一些示例数据，以获取有关问题的更多详细信息：
我使用了Gordon Linoff的答案提供的查询...但是它也无法正常工作，并非我所有的返回数据都正确正确。
我结果中的许多行不包含第一个/最早的时间戳。这是返回的行，可按预期工作：
| 3d16632fe65e2155db0bd3304bae8ad2 | 2019-01-31 15:01:38+00 | signup | https://www.google.com/ | /community/.... | |
当我在跟踪表中搜索此cookie_hash时，我可以通过以下方式证明这一点：
+---------+----------------------------------+------------------------+---------------+--------+-----------------+-----------------+----------------+-------------------+----------------------------------------------------------+-------------------------+------------------------------------------------------------------------------------------------------------
| id | cookie_hash | crdate | remote_addr | uri | refererhost | refererscheme | refererquery | refererfragment | refererpath | http_referer | http_user_agent
|---------+----------------------------------+------------------------+---------------+--------+-----------------+-----------------+----------------+-------------------+----------------------------------------------------------+-------------------------+------------------------------------------------------------------------------------------------------------
| 1182240 | 3d16632fe65e2155db0bd3304bae8ad2 | 2019-01-31 15:01:38+00 | xxx | <null> | example.io | https | <null> | <null> | /community/.... | https://www.google.com/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.0.2 Saf
| 1182241 | 3d16632fe65e2155db0bd3304bae8ad2 | 2019-01-31 15:01:47+00 | xxx | <null> | ....io | https | <null> | <null> | /signup/ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.0.2 Saf
| 1182248 | 3d16632fe65e2155db0bd3304bae8ad2 | 2019-01-31 15:04:52+00 | xxx | <null> | example.io | https | <null> | <null> | /community/.... | https://www.google.com/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.0.2 Saf
+---------+----------------------------------+------------------------+---------------+--------+-----------------+-----------------+----------------+-------------------+----------------------------------------------------------+-------------------------+------------------------------------------------------------------------------------------------------------
但是，许多返回的行不正确，我回来了，cookie_hashes没有第一个命中的'/community％'和http_referer with null ...看这个示例行：
| 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 11:46:49+00 | signup | https://www.google.de/ | /community/.../.../ |
当我在跟踪表中查找此跟踪时，我会得到以下结果：
+--------+----------------------------------+------------------------+---------------------------------------+--------+-----------------+-----------------+----------------+-------------------+---------------------------------------------------+---------------------------------+------------------------------------------------------------------------------------
| id | cookie_hash | crdate | remote_addr | uri | refererhost | refererscheme | refererquery | refererfragment | refererpath | http_referer | http_user_agent
|--------+----------------------------------+------------------------+---------------------------------------+--------+-----------------+-----------------+----------------+-------------------+---------------------------------------------------+---------------------------------+------------------------------------------------------------------------------------
| 528636 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:03:12+00 | xxx | <null> | example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528637 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:03:19+00 | xxx | <null> | example.io | https | <null> | <null> | / | https://example.io/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528638 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:03:30+00 | xxx | <null> | example.io | https | <null> | <null> | /bla/team/ | https://example.io/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528690 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:24:47+00 | xxx | <null> | my.example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528692 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:26:01+00 | xxx | <null> | my.example.io | https | <null> | <null> | /signup/ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528693 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:26:06+00 | xxx | <null> | my.example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528695 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:26:13+00 | xxx | <null> | my.example.io | https | <null> | <null> | /signup/ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528700 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:26:41+00 | xxx | <null> | my.example.io | https | <null> | <null> | /xxx/ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528701 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:27:17+00 | xxx | <null> | my.example.io | https | <null> | <null> | /ValidateSuccess | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528702 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:27:22+00 | xxx | <null> | my.example.io | https | <null> | <null> | /xxx/ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528703 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:27:45+00 | xxx | <null> | my.example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528705 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:27:56+00 | xxx | <null> | example.io | https | <null> | <null> | /community/.../ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528721 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 09:30:45+00 | xxx | <null> | example.io | https | <null> | <null> | /bla/team/ | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528847 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 10:41:34+00 | xxx | <null> | example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528848 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 10:41:38+00 | xxx | <null> | example.io | https | <null> | <null> | /bla/team/ | https://example.io/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 528999 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 11:46:49+00 | xxx | <null> | example.io | https | <null> | <null> | /community/.../.../ | https://www.google.de/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 529016 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 11:57:00+00 | xxx | <null> | example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 529017 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 11:57:04+00 | xxx | <null> | example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 529171 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 13:15:59+00 | xxx | <null> | example.io | https | <null> | <null> | / | <null> | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 529172 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 13:16:02+00 | xxx | <null> | example.io | https | <null> | <null> | /bla/ | https://example.io/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
| 529173 | 9edf070706c8a728cc78719befc6cdd7 | 2018-01-08 13:16:04+00 | xxx | <null> | example.io | https | <null> | <null> | /bla/team/ | https://example.io/bla/ | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/604.4.7 (KHTML, like Ge
+--------+----------------------------------+------------------------+---------------------------------------+--------+-----------------+-----------------+----------------+-------------------+---------------------------------------------------+---------------------------------+------------------------------------------------------------------------------------
您是否要过滤 distinct on？
SELECT x.*
FROM (SELECT DISTINCT ON (cookie_hash) t1.cookie_hash, t1.crdate, event.action, t1.http_referer, t1.refererpath
FROM (SELECT t1.*, MIN(crdate) OVER (PARTITION BY id) as min_crdate
FROM trace t1
) t1 INNER JOIN
event
ON t1.cookie_hash = event.cookie_hash
WHERE t1.crdate = t1.min_crdate AND
refererpath LIKE '/community%' AND
http_referer IS NOT NULL AND
http_referer NOT LIKE 'https://example.io%'
ORDER BY cookie_hash, crdate;
) x
WHERE action = 'signup'