Блог

У меня есть SQL-запрос Snowflake, который я пытаюсь выполнить в R через ODBC-соединение, который выглядит следующим образом

 SET quiet=TRUE;

USE SOMEDATABASE.SOMESCHEMA;

--Select timestamp of last sale per customer
DROP TABLE IF EXISTS sales;
CREATE TEMPORARY TABLE sales(CustomerId VARCHAR(16777216), SaleTS TIMESTAMP_NTZ(9));

INSERT INTO sales
SELECT CustomerId, 
       SaleTS
FROM SALES
WHERE SaleTS>= '2020-11-19 00:00:00'
AND SaleTS <= '2020-11-19 23:59:59.999'
GROUP BY CustomerId;

--Use temp table to get correct row from sales table
SELECT  SUM(SalesDetail.price) as SumPrice
        COUNT(*) as SoldVolume
FROM sales
LEFT JOIN SALES as SalesDetail
    ON Sales.CustomerId = SalesDetail.CustomerId 
    AND sales.SaleTS = SalesDetail.SaleTS 
 

Запрос Microsoft SQL Server из R, который я обычно включаю set nocount no; в начало запроса, чтобы гарантировать, что в R возвращается только последний шаг, чтобы избежать ошибки Actual statement count 6 did not match the desired statement count 1. Ошибка имеет смысл, SQL возвращает 6 компонентов, когда R ожидает 1 (6 по одному для каждого шага в моем SQL-запросе). В Snowflake, похоже, нет возможности установить nocount таким же образом. Мой вопрос заключается в том, как мне избежать вышеуказанной ошибки. Есть ли у кого-нибудь опыт выполнения многоступенчатого SQL-запроса Snowflake через R? Как я могу заставить R получать только последнее утверждение от соединения ODBC. До сих пор я пробовал set nocount=TRUE; , set echo=FALSE; , set message=FALSE; , SET quiet=TRUE и т. Д

Snowflake SQL достаточно выразителен, и предлагаемый код может быть структурирован как один запрос:

 WITH cte AS (
    SELECT CustomerId, MAX(SaleTS) AS SaleTS  -- here agg function is required
    FROM SALES
    WHERE SaleTS>= '2020-11-19 00:00:00'
    AND SaleTS <= '2020-11-19 23:59:59.999'
    GROUP BY CustomerId
)
SELECT  SUM(SalesDetail.price) as SumPrice
        COUNT(*) as SoldVolume
FROM cte
LEFT JOIN SALES as SalesDetail
    ON Sales.CustomerId = SalesDetail.CustomerId 
    AND sales.SaleTS = SalesDetail.SaleTS;
 

Исходный запрос использует одно и то же имя как для таблицы, так и для временной таблицы, отличаясь только регистром sales vs SALES , что может привести к ошибкам.

Во-вторых: база данных и схема могут быть настроены во время установления соединения, поэтому нет необходимости во USE внутреннем скрипте. В качестве альтернативы в скрипте может использоваться полное имя.

Я предполагаю, что цель запроса заключается в следующем:

 WITH cte AS (
  SELECT *
  FROM SOMEDATABASE.SOMESCHEMA.SALES
  WHERE SaleTS BETWEEN '2020-11-19 00:00:00' AND '2020-11-19 23:59:59.999'
  QUALIFY ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY SaleTS DESC) = 1
)
SELECT COUNT(*) AS SoldVolume, SUM(price) as SumPrice
FROM cte;
 

Если возможно, что у одного человека есть две записи для одних и тех же продаж, тогда RANK() OVER(...) их следует использовать вместо этого.