kecookier commented on issue #7024: URL: https://github.com/apache/incubator-gluten/issues/7024#issuecomment-2311538581
sql
```
SELECT p1.*,p2.flag
FROM (
SELECT linkid,
direction,
hot,
hot*1000/link_length AS avg_hot,
link_length,
geometry,
adcode
FROM (
SELECT a.linkid,
direction,
adcode,
count(DISTINCT a.userid) AS hot
FROM (
SELECT s_linkid AS linkid,
direction,
substr(town_adcode,1,6) adcode,
userid
FROM leftTableA
WHERE dt='20240819'
AND s_linkid=lag_s_linkid
AND speed<10
UNION ALL SELECT linkid,
direction,
substr(town_adcode,1,6) adcode,
userid
FROM rightTableB
WHERE dt='20240819'
AND ((link_con>2 AND LENGTH/dis<=1.5) OR
link_con=2)
AND link_con<=5
AND dis>15
AND speed<10
)a
JOIN (
SELECT *
FROM (
SELECT userid,
linkid,
max(unix_timestamp(TIME,'yyyy-MM-dd
HH:mm:ss')) AS max_t,
min(unix_timestamp(TIME,'yyyy-MM-dd
HH:mm:ss')) AS min_t,
max(unix_timestamp(TIME,'yyyy-MM-dd
HH:mm:ss'))-min(unix_timestamp(TIME,'yyyy-MM-dd HH:mm:ss')) AS time_dis,
count(DISTINCT direction) AS type_con,
count(1) con
FROM (
SELECT s_linkid AS linkid,
direction,
TIME,
userid
FROM leftTableA
WHERE dt='20240819'
AND s_linkid=lag_s_linkid
AND speed<10
UNION ALL SELECT linkid,
direction,
TIME,
userid
FROM rightTableB
WHERE dt='20240819'
AND ((link_con>2 AND
LENGTH/dis<=1.5) OR link_con=2)
AND link_con<=5
AND dis>15
AND speed<10
)a
GROUP BY userid,
linkid
)
WHERE !(type_con>1 AND time_dis<600)
)b
ON a.userid=b.userid
AND a.linkid=b.linkid
GROUP BY a.linkid,
direction,
adcode
)t1
JOIN (
SELECT *
FROM tableC
WHERE dt=(
SELECT max(dt)
FROM tableC
WHERE dt LIKE "%.%00p00"
)
)t2
ON t1.linkid=cast(t2.link_id AS string)
)p1
JOIN (SELECT link_id,flag
FROM (
SELECT *,
LinkFilter(link_type,link_tag,link_level) AS flag
FROM tabled
WHERE dt=(
SELECT max(dt)
FROM tabled
WHERE product_dt<='20240819'
)
)t
)p2
ON p1.linkid=p2.link_id
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
