在形成SQL查询方面需要帮助 [英] Need help in forming a SQL query
本文介绍了在形成SQL查询方面需要帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我们有两个名为tbl1和tbl2的表。它包含诸如ACCESS_ID、CUSTOMER ID等列。在某些情况下,一个VISTICE_ID将与多个客户ID关联。
例如,如果客户登录到某个网站,则他每次访问该网站时都会生成一个唯一的VIESS_ID。
在一次访问中,多个客户可以登录到其帐户并进行单独购买。
在某些情况下,一次访问将与多个客户ID相关联。如果有2个以上的实例,请在此列中附加任何其他零售客户ID实例。对于访问实例,该访问附加了200个客户ID。
例如,如果在一次访问中有7个客户ID,则对于客户1, 它应该有第一个客户%1。对于客户%2,我们将需要显示第二个客户ID。对于第3到7项,这5项都将用逗号分隔。
是否有人可以帮助如何使用此逻辑构建SQL查询?
with CTE as (
SELECT
visit_id,
B.visitpg_nbr::INT AS visitpg_nbr,
CUSTOMER_ID,
dense_rank()over( PARTITION BY VISIT_ID order by CUSTOMER_ID) as rank
from
db_name.schema_name.tbl_1 A
JOIN db_name.schema_name.tbl_2 B
ON B.id_column = A.id_column
JOIN db_name.schema_name.tbl_3 C
ON CAST(C.xid as VARCHAR)= A.CUSTOMER_ID
WHERE flg_col = '0'
AND so_cd NOT IN ('0','1','2','3')
AND DATE_COL = '2022-01-17'
and visit_id='12345'
ORDER BY visitpg_nbr
)
select VISIT_ID, arr[0], arr[1], array_to_string( array_slice(arr, 2, 99999), ', ')
from (
select VISIT_ID, array_agg(distinct CUSTOMER_ID) within group(order by CUSTOMER_ID) arr
from CTE
group by 1
);
感谢那些已经回复的人。我真的很感谢他们的指导。这种逻辑运作得很好。当我加入CTE内的3张桌子时,我得到了很多重复的东西。我要消除重复值。
当我运行CTE中包含的以下查询时,我得到的记录是重复的。
SELECT
visit_id,
B.visitpg_nbr::INT AS visitpg_nbr,
CUSTOMER_ID,
dense_rank()over( PARTITION BY VISIT_ID order by CUSTOMER_ID) as rank
from
db_name.schema_name.tbl_1 A
JOIN db_name.schema_name.tbl_2 B
ON B.id_column = A.id_column
JOIN db_name.schema_name.tbl_3 C
ON CAST(C.xid as VARCHAR)= A.CUSTOMER_ID
WHERE flg_col = '0'
AND so_cd NOT IN ('0','1','2','3')
AND DATE_COL = '2022-01-17'
and visit_id='12345'
ORDER BY visitpg_nbr
Row VISIT_ID CUSTOMER_ID VISITPG_NBR RANK
**1 12345 100 1 1**
2 12345 100 2 1
3 12345 100 3 1
4 12345 100 4 1
5 12345 100 5 1
**6 67891 101 6 2**
7 67891 101 7 2
8 67891 101 8 2
9 67891 101 9 2
10 67891 101 10 2
**11 78910 102 11 3**
12 78910 102 12 3
13 78910 102 13 3
14 78910 102 14 3
是否有在CTE临时表中显示不同结果的逻辑?
应按如下方式填充最终结果。
VISIT_ID First_Customer Second_Customer Other_Customers
1 100 101 102,103,104,105,106
2 200 201 202,203,204,205
第一个Customer_ID应显示在First_Customer列中,Second_Customer_ID应显示在Second_Customer列中。所有其他Customer_ID应显示在最后一列中,并且应用逗号分隔。
另外,我还希望结果按visitpg_nbr
排序推荐答案
与Felipe的回答略有不同,不确定哪个性能更好。我怀疑是他的,但不管怎样,这里有另一种尝试的方式。
SELECT visit_id, first_customer, second_customer
,array_agg(other_ids) within group (order by order_id) as other_customer
FROM(
SELECT visit_id,
order_id,
first_value(customer_id) over (partition by visit_id order by order_id) as first_customer,
first_value(customer_id) over (partition by visit_id order by order_id) as second_customer,
IFF(row_number() over (partition by visit_id order by order_id) > 2, customer_id, null) as other_ids
FROM VALUES
(1,100, 1),
(1,101, 2),
(1,102, 3),
(1,103, 5),
(1,104, 6),
(1,105, 6),
(1,106, 7),
(2,200, 1),
(2,201, 2),
(2,202, 3),
(2,203, 4)
v(visit_id, customer_id, order_id)
)
GROUP BY 1,2,3
ORDER BY 1,2,3;
访问ID | First_Customer | Second_Customer | Other_Customer |
---|---|---|---|
1 | 100 | 100 | [102,103,104,105,106] |
2 | 200 | 200 | [202,203] |
这篇关于在形成SQL查询方面需要帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文