Changeset: 87b9cbc18aaa for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/87b9cbc18aaa
Modified Files:
sql/test/nested/Tests/webclicks.test.in
Branch: nested
Log Message:
adding webclicks complete qry with some selections disabled
diffs (110 lines):
diff --git a/sql/test/nested/Tests/webclicks.test.in
b/sql/test/nested/Tests/webclicks.test.in
--- a/sql/test/nested/Tests/webclicks.test.in
+++ b/sql/test/nested/Tests/webclicks.test.in
@@ -179,6 +179,22 @@ FROM events
----
60
+query T
+select distinct(l.element.key)
+from events
+left join unnest(location.list) as l on true
+----
+hash
+hostname
+pageSubType
+pageType
+pathname
+protocol
+referrer
+search
+state
+title
+
query TTTTTTTTTT
SELECT
timestamp,
@@ -198,6 +214,83 @@ FROM events
cross join unnest("user".list) as u
----
+statement ok
+CREATE FUNCTION pcre_match(s string, pattern string) RETURNS boolean EXTERNAL
NAME pcre."match"
+
+# this is not exactly the functionality intended
+# in the large query to follow
+statement ok
+create function REGEXP_EXTRACT(s string, pattern string)
+returns string
+begin
+ if (pcre_match(s, pattern))
+ then return s;
+ else return null;
+ end if;
+end
+
+query TTTTTT
+WITH base AS (
+ SELECT
+ timestamp,
+ sessionid,
+ userid,
+ user_agent,
+ event,
+ ecommerce.name as ecom_name
+ --CASE WHEN l.element.key = 'pageType' THEN l.element.value END AS
pagetype
+ --CASE WHEN u.element.key = 'customerType' THEN u.element.value END AS
customertype,
+ --CASE WHEN u.element.key = 'customerid' THEN u.element.value END AS
customerid,
+ --CASE WHEN eal.element.key = 'brand' THEN eal.element.value END AS
ecom_attributes_brand
+ FROM events
+ left join unnest(location.list) as l on true
+ left join unnest(ecommerce.objects.list) as eol on true
+ left join unnest(eol.element.attributes.list) as eal on true
+ cross join unnest("user".list) as u
+ WHERE cast(timestamp as date) = date'2024-11-30'
+ AND userid != 'na' AND userid != ''
+ AND sessionid != 'na' AND sessionid != '')
+, last_datapoints AS (
+ SELECT
+ sessionid,
+ userid,
+ timestamp,
+ event,
+ --max(pagetype) as pagetype,
+ max(user_agent) as user_agent,
+ max(ecom_name) as ecom_name
+ --max(customertype) as customertype,
+ --max(ecom_attributes_brand) as ecom_attributes_brand,
+ --max(customerid) as customerid
+ FROM base
+ group by sessionid, userid, timestamp, event
+)
+select event,
+ ecom_name,
+ IFNULL(REGEXP_EXTRACT(user_agent, '.*Edg.*'), 'No edge') as
edge_user_agent,
+ cast(timestamp as date) as date,
+ --customertype,
+ count(distinct(userid)) as users,
+ --count(distinct(customerid)) as customers,
+ --count(distinct(pagetype)) as pagetypes,
+ count(distinct(concat(userid, sessionid))) as sessions
+from (
+ SELECT *,
+ ROW_NUMBER() OVER (PARTITION BY sessionid, userid, event ORDER BY
timestamp ASC) as row_num
+ FROM last_datapoints
+)
+where row_num=1
+group by 1, 2, 3, 4
+order by 3 desc
+----
+productDetail
+detail
+No edge
+2024-11-30
+1
+1
+
+
#WITH base AS (
# SELECT
# timestamp,
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
