Работа с дубликатами столбцов в DuckDB с пробелами (нулями) и их эффективное заполнение ⇐ Python

[Anonymous]

Я новичок в Duckdb (v0.10.1), поэтому этот вопрос возникает из-за моего незнания встроенных функций DuckDB.
У меня есть особый вариант использования, в котором я не нашел более удобного способа сделать это с DuckDB с данными временных рядов. Иногда бывают редкие случаи, когда мы получаем повторяющиеся значения временной метки в таблице, которую нам необходимо дедупировать. Проблема здесь в том, что иногда повторяющиеся строки имеют разные пробелы для разных столбцов, которые при объединении заполняют большую часть/все пробелы, если мы объединяем строки. Вот пример, иллюстрирующий ситуацию.
Таблица с повторяющимися значениями для столбца метки времени в следующей таблице.



метка времени (varchar)
A (int64)
B (int64)




2022-01-01
1
NULL


2022-01-01
NULL
2


2022-01-02
3
6


2022-01-02
4
NULL



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



метка времени (varchar)
A (int64)
B (int64)




01.01.2022
1
2


2022-01-02
3
6



Используя pandas, это можно эффективно сделать, используя следующую логику:

Код: Выделить всё

# Fix filling issues for rows with the same timestamp
df = df.set_index("timestamp", drop=False)
df_first = df[~df.index.duplicated(keep="first")]
df_last = df[~df.index.duplicated(keep="last")]
df = df_first.fillna(df_last)

Здесь мы извлекаем в отдельные фреймы данных первую повторяющуюся строку и последнюю, а затем пытаемся заполнить пробелы в первом фрейме данных вторым.
Используя DuckDB, я не смог найти достаточно умного решения для этого, кроме агрегирования всех возможных столбцов в пару с меткой времени и сшивания их всех вместе в запросе, подобном следующему:

Код: Выделить всё

# Construct the SQL query
subqueries = []
for column in list(df.columns).remove('timestamp'):
subquery = f"""
(
SELECT timestamp, {column}
FROM (
SELECT timestamp, {column},
ROW_NUMBER() OVER(PARTITION BY timestamp ORDER BY (CASE WHEN {column} IS NULL THEN 1 ELSE 0 END)) as rn
FROM df
) sub
WHERE rn = 1
) AS {column}
"""
subqueries.append(subquery)

#query = "SELECT " + ", ".join([f"{column}.timestamp, {column}.{column}" for column in column_names])
query = "SELECT " + ", ".join([f"{column_names[0]}.timestamp"] + [f"{column}.{column}" for column in column_names])
query += " FROM " + subqueries[0]

for i in range(1, len(subqueries)):
query += f" JOIN {subqueries[i]} ON {column_names[0]}.timestamp = {column_names[i]}.timestamp"

Выходной запрос этого кода следующий:

Код: Выделить всё

    SELECT A.timestamp, A.A, B.B FROM
(
SELECT timestamp, A
FROM (
SELECT timestamp, A,
ROW_NUMBER() OVER(PARTITION BY timestamp ORDER BY (CASE WHEN A IS NULL THEN 1 ELSE 0 END)) as rn
FROM df
) sub
WHERE rn = 1
) A
JOIN
(
SELECT timestamp, B
FROM (
SELECT timestamp, B,
ROW_NUMBER() OVER(PARTITION BY timestamp ORDER BY (CASE WHEN B IS NULL THEN 1 ELSE 0 END)) as rn
FROM df
) sub
WHERE rn = 1
) B
ON A.timestamp = B.timestamp

Это дает правильный результат, но требует значительного увеличения использования памяти и занимает намного больше времени по сравнению с решением с использованием панд. Кроме того, запросы становятся довольно большими при наличии кадров данных со многими столбцами.
Текущая работа, которую я использую, чтобы справиться с этой проблемой, заключается в том, чтобы просто перейти к pandas, когда это необходимо, чтобы запустить логику дедупликации, а затем вернуться к DuckDB.
Мне интересно, есть ли у кого-нибудь лучший способ сделать это, который может быть таким же производительным, как реализация pandas.
Ура!
Я пытался использовать только sql для решения проблемы в DuckDB, поскольку встроенные функции не подходили для того, что я искал.>

Подробнее здесь: https://stackoverflow.com/questions/783 ... hem-effici