sql - SQL duplicated visitors per product

Question

I have a Main table and visitors and I link them up by Date and Hour( I have only Date, hour and Visitors in Visitors table)

This is my table

DATE        HOUR  ORDER_ID   ITEM     VISITORS
2018-09-25  14    123456     Box      140
2018-09-25  14    345677     Paper    140
2018-09-25  14    234678     Bear     140

BUT I need somehow to make it like this, to remove duplicated visitors, is it possible at all?

DATE        HOUR  ORDER_ID   ITEM     VISITORS
2018-09-25  14    123456     Box      140
2018-09-25  14    345677     Paper    
2018-09-25  14    234678     Bear     

I'm looking for logic to understand how to do it. As I need Visitors in the same table

标签： sqlgoogle-bigquery

Answer 1

你似乎想要：

select date, hour, order_id, item,
       (case when row_number() over (partition by date, hour order by order_id) = 1
             then visitors
        end) as visitors
from t;

这将visitors只包括给定日期和时间的一行。