Presto SQL技术咨询：如何横向合并两个结构完全不同的表而非纵向合并

Got it, the issue here is that UNION stacks results vertically (adding rows), but you want your two totals to appear as separate columns in the same row. Here are two straightforward ways to adjust your SQL query to get the output you're after:

Option 1: Subqueries in the SELECT Clause #

This approach treats each sum as a standalone subquery that returns a single value, which we then label as columns:

QUERY = """
SELECT
  (SELECT SUM(CAST(US.amount as DECIMAL(13,2))) FILTER (WHERE type IN ('USA')) FROM da1.tb1) AS "USA Net Amount",
  (SELECT SUM(CAST(EU.amount as DECIMAL(13,2))) FILTER (WHERE type IN ('EU')) FROM da2.tb2) AS "EU Net Amount"
"""
results = fetch_data(QUERY)
display(results)
results.to_csv('output.csv', index=False)

Option 2: Cross Join Single-Row Results #

Since each of your original queries returns exactly one row (the sum), you can cross join them to combine their columns into a single row:

QUERY = """
SELECT
  usa_totals."USA Net Amount",
  eu_totals."EU Net Amount"
FROM
  (SELECT SUM(CAST(US.amount as DECIMAL(13,2))) FILTER (WHERE type IN ('USA')) AS "USA Net Amount" FROM da1.tb1) usa_totals
CROSS JOIN
  (SELECT SUM(CAST(EU.amount as DECIMAL(13,2))) FILTER (WHERE type IN ('EU')) AS "EU Net Amount" FROM da2.tb2) eu_totals
"""
results = fetch_data(QUERY)
display(results)
results.to_csv('output.csv', index=False)

Why This Works #

Both methods take your two independent summary calculations and combine them horizontally instead of vertically. The result will be a single row with two columns, exactly matching your desired output:

Your existing Python workflow doesn't need any other changes—just swap out the QUERY variable with either of the above versions.

内容的提问来源于stack exchange，提问作者Alex