我正在尝试创建一个用于 Quicksight 分析的数据集。
我正在 Postgresql 数据源上使用自定义查询。
每当我在查询中使用 Postgresql 函数时就会出现问题。
事实上，相同的查询确实适用于 IntelliJ DataGrip 和 PgAdmin，但我收到一个非常意外的错误，如下所示：
QuickSight could not generate any output column after applying transformation. Check transform(s) and try again.
SELECT
users.name AS "User",
accounts.id AS "Account ID",
groups.name AS "Group",
account_balances.balance AS "Account Balance",
(
SELECT
account_balances.balance
FROM
account_balances
WHERE
account_balances.account_id = (
SELECT
accounts.id
FROM
accounts
WHERE
accounts.user_id = users.id
AND
accounts.account_type_id = 11
)
) AS "D Account Balance",
(
SELECT
decimal_value
FROM
user_custom_field_values
WHERE
user_custom_field_values.field_id = (
SELECT
user_custom_fields.id
FROM
user_custom_fields
WHERE
user_custom_fields.internal_name = 'capacity'
)
AND
owner_id = users.id
) AS "Capacity",
(
SELECT
decimal_value
FROM
user_custom_field_values
WHERE
user_custom_field_values.field_id = (
SELECT
user_custom_fields.id
FROM
user_custom_fields
WHERE
user_custom_fields.internal_name = 'Turnover'
)
AND
owner_id = users.id
) AS "Turnover",
(
SELECT value
FROM
user_custom_field_possible_values
WHERE
id in (
SELECT
possible_value_id
FROM
user_enum_values
WHERE
owner_id in (
SELECT
user_custom_field_values.id
FROM
user_custom_field_values
JOIN
user_custom_fields ON user_custom_fields.id=user_custom_field_values.field_id
WHERE
owner_id = users.id
AND
user_custom_fields.internal_name = 'contractType'
)
)
LIMIT 1
) AS "Contract Type",
(
SELECT
COUNT(id)
FROM
ads
WHERE
owner_id = users.id
AND
(ads.creation_date BETWEEN NOW() - interval '6 months' AND NOW())
) AS "ADs last 6 months",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.to_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
AND
(transfers.date BETWEEN NOW() - interval '3 months' AND NOW())
) AS "Sells last 3 months",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.to_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
AND
(transfers.date BETWEEN NOW() - interval '6 months' AND NOW())
) AS "Sells last 6 months",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.to_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
AND
(transfers.date BETWEEN NOW() - interval '12 months' AND NOW())
) AS "Sells last years",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.to_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
) AS "Sells",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.from_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
AND
(transfers.date BETWEEN NOW() - interval '3 months' AND NOW())
) AS "Purchases last 3 months",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.from_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
AND
(transfers.date BETWEEN NOW() - interval '6 months' AND NOW())
) AS "Purchases last 6 months",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.from_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
AND
(transfers.date BETWEEN NOW() - interval '12 months' AND NOW())
) AS "Purchases last year",
(
SELECT
SUM(transfers.amount)
FROM
transfers
WHERE
transfers.from_id = accounts.id
AND
transfers.chargeback_of_id IS NULL
AND
transfers.charged_back_by_id IS NULL
) AS "Purchases",
(
SELECT
account_limit_logs.credit_limit
FROM
account_limit_logs
WHERE
account_limit_logs.account_id = accounts.id
ORDER BY
account_limit_logs.id DESC
LIMIT 1
) AS "Account Limit"
FROM
users
JOIN
accounts ON accounts.user_id = users.id
JOIN
groups ON groups.id = users.user_group_id
JOIN
account_balances ON accounts.id = account_balances.account_id
WHERE
accounts.account_type_id = 4
问题似乎是- interval 'fooBar'部分。
没有它，查询就会顺利进行。
有谁对 AWS Quicksight 及其秘密有足够深入的了解，愿意提供帮助吗？
即使使用不同的 SQL 解决方案来获得相同的结果也是可以接受的。
Thanks!
