Блог

Я пытаюсь получить список всех пользователей в базе данных. Тогда у меня есть другая таблица, в которой у меня есть только пользователи, которые являются участниками.

Проблема в том, что некоторые из тех, кто является участниками сегодня, могли ранее быть клиентами, участниками или вообще никем из них. Таким образом, у нас могут быть дубликаты.

Что я хочу сделать, так это выбрать только самую последнюю запись на основе столбца даты, который присутствует в базе данных.

Вот выходные данные 2 таблиц:

Пользовательская таблица:

Таблица пользователей

Таблица членов: Таблица членов

Хотите объединить таблицы слева, сохранив все отдельные записи из таблицы users и большинство совпадающих записей из таблицы members с самым последним cd.value.

     WITH users AS(

SELECT
fullVisitorId AS Clientid

FROM `records`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
totals.visits = 1


), members As(

SELECT
MAX(date) AS date,
fullVisitorId AS Clientid,
cd.value AS CD_value,
cd.index AS CD_index

FROM `records`,
UNNEST(customDimensions) AS cd
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
totals.visits = 1
AND
cd.index = 6
group by
Clientid,
CD_value,
CD_index


)

SELECT
users.ClientId AS clientId,
members.CD_value

from users


LEFT JOIN members ON users.ClientId = members.Clientid


group by
members.CD_value,
clientId


order by
clientId ASC
  

попробуйте с помощью row_number()

 WITH users AS(

SELECT
fullVisitorId AS Clientid

FROM `records`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
totals.visits = 1


), members As(

SELECT
date AS date,
fullVisitorId AS Clientid,
cd.value AS CD_value,
cd.index AS CD_index,
row_number() over(partition by Clientid,
CD_value,
CD_index order by date desc) rn

FROM `records`,
UNNEST(customDimensions) AS cd
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
totals.visits = 1
AND
cd.index = 6 


), m2 as ( select * from members where rn=1)

SELECT distinct
users.ClientId AS clientId,
m2.CD_value

from users  

LEFT JOIN m2 ON users.ClientId = m2.Clientid  


order by
clientId ASC