时间戳

首页 > 解决方案 > 在 Power BI 中使用临时表导入数据

sql - 在 Power BI 中使用临时表导入数据

问题描述

我想使用以下 SQL 查询将数据导入 Power BI。它涉及创建临时表并将该表用作主要数据源。如何在 Power BI 中执行此操作？从数据库加载数据时，我尝试在编辑器中使用此查询，但我不断收到这样的错误 在此处输入图像描述

我基本上使用了这个数据集https://www.kaggle.com/kyanyoga/sample-sales-data并将其加载到 postgressql 数据库中。

-- 1. Create temp table to house temporary results
DROP TABLE IF EXISTS product_quantity;
CREATE TEMP TABLE product_quantity
 (product_line varchar, this_month_quantity integer, last_month_quantity integer)

 --2. Quantity ordered for each Product line for current month is inserted into temporary table.
INSERT INTO product_quantity (product_line, this_month_quantity, last_month_quantity)
SELECT "productline", SUM("quantityordered"), 0
FROM test_schema.sales_data_sample
where "month_id" = 3 and "year_id" = 2003
GROUP BY "productline";

--3. Quantity ordered for each Product line for last month is inserted into temporary table.
INSERT INTO product_quantity (product_line, this_month_quantity, last_month_quantity)
SELECT "productline", 0, SUM("quantityordered")
FROM test_schema.sales_data_sample
where "month_id" = 2 and "year_id" = 2003
GROUP BY "productline";

--4. Retrieve required results.
select
    "product_line",
    sum("this_month_quantity") as "this_month_quantity",
    sum("last_month_quantity") as "last_month_quantity"
FROM product_quantity
group by "product_line"

标签： sqlpostgresqlpowerbi

解决方案

此查询是否运行没有错误？

我已将您的查询转换为一个大的内联查询。

select
    ST."product_line",
    sum(ST."this_month_quantity") as "this_month_quantity",
    sum(ST."last_month_quantity") as "last_month_quantity"
FROM
(
    SELECT "productline", 
    SUM("quantityordered") as this_month_quantity, 
    0 as last_month_quantity
    FROM test_schema.sales_data_sample
    where "month_id" = 3 and "year_id" = 2003
    GROUP BY "productline"
    UNION ALL
    SELECT "productline", 
    0, 
    SUM("quantityordered")
    FROM test_schema.sales_data_sample
    where "month_id" = 2 and "year_id" = 2003
    GROUP BY "productline"
) as ST
group by ST."product_line"

（请注意，我只是猜测了转换 - 我没有要测试的 postgresql）

推荐阅读