Я использую PostgreSql, и я новичок в хранимых функциях. Я хочу, чтобы моя хранимая функция возвращала несколько столбцов.
Следующие входные данные отражают столбцы исходной таблицы, в которой хранятся данные от разных пользователей (например, clicks_a, clicks_b, clicks_c, cost_a, cost_b, cost_c и т. д.).
Входные данные: клики, стоимость, бронирования, booking_rev.
Желаемый результат:
month | ttt_group | cpc | monthly_clicks_mln | cvr | margin_per_conversion | monthly_profit | break_even_cpc | ideal_ccp
Я написал следующий код, но я получаю эту ошибку:
ОШИБКА: ссылка на столбец market_segment неоднозначна.
Код:
CREATE OR REPLACE FUNCTION user_performance
(clicks CHARACTER VARYING, cost CHARACTER VARYING,
bookings CHARACTER VARYING, booking_rev CHARACTER VARYING)
RETURNS table(month DOUBLE PRECISION, market_segment CHARACTER VARYING,
cpc TEXT, monthly_clicks_mln NUMERIC(10,3), cvr TEXT,
margin_per_conversion TEXT, monthly_profit MONEY, break_even_cpc TEXT, ideal_ccp TEXT)
AS $BODY$
BEGIN
WITH monthly_activity AS (
SELECT EXTRACT(MONTH FROM date) AS month, market_segment,
CASE WHEN clicks = 'clicks_a' THEN SUM(clicks_a)
WHEN clicks = 'clicks_b' THEN SUM(clicks_b)
WHEN clicks = 'clicks_c' THEN SUM(clicks_c)
END AS monthly_clicks,
CASE WHEN cost = 'cost_a' THEN SUM(cost_a)
WHEN cost = 'cost_a' THEN SUM(cost_b)
WHEN cost = 'cost_a' THEN SUM(cost_c)
END AS monthly_cost,
CAST(CASE WHEN cost = 'cost_a' AND clicks = 'clicks_a'
THEN (SUM(cost_a) :: FLOAT /SUM(clicks_a) :: FLOAT)
WHEN cost = 'cost_b' AND clicks = 'clicks_b'
THEN (SUM(cost_b) :: FLOAT /SUM(clicks_b) :: FLOAT)
WHEN cost = 'cost_c' AND clicks = 'clicks_c'
THEN (SUM(cost_c) :: FLOAT /SUM(clicks_c) :: FLOAT)
END AS DECIMAL(5,4)) AS cpc,
CASE WHEN bookings = 'bookings_a' THEN SUM(bookings_a)
WHEN bookings = 'bookings_b' THEN sum(bookings_b)
WHEN bookings = 'bookings_c' THEN sum(bookings_c)
END AS monthly_bookings,
CASE WHEN booking_rev = 'booking_rev_a' THEN SUM(booking_rev_a)*15/100
WHEN booking_rev = 'booking_reb_b' THEN SUM(booking_rev_b)*15/100
WHEN booking_rev = 'booking_reb_c' THEN SUM(booking_rev_c)*15/100
END AS monthly_margin
FROM master_table_2019
GROUP BY month, market_segment
ORDER BY month
),
adv_metrics AS (
SELECT
month,market_segment,
CAST(AVG(cpc) AS DECIMAL(10,3)) AS cpc,
CAST(AVG(monthly_clicks)/1000000.0 AS DECIMAL(10,3)) AS monthly_clicks_mln,
CAST(MAX(100.0*monthly_bookings :: FLOAT /monthly_clicks :: FLOAT) AS DECIMAL(3,2)) AS cvr,
CAST(AVG(monthly_margin :: FLOAT /monthly_bookings :: FLOAT) AS DECIMAL(10,3)) AS margin_per_conversion,
MAX(monthly_margin - monthly_cost) AS monthly_profit
FROM monthly_activity
GROUP BY month, market_segment
ORDER BY month
)
SELECT
month, market_segment,
CONCAT(cpc,' €') AS cpc,
monthly_clicks_mln,
CONCAT(cvr,'%') AS cvr,
CONCAT(margin_per_conversion,' €') AS margin_per_conversion,
MONEY(monthly_profit) AS monthly_profit,
CONCAT(CAST((margin_per_conversion * cvr/100.0) AS DECIMAL(4,3)),' €') AS break_even_cpc,
CONCAT(CAST((margin_per_conversion * cvr/100.0)*0.85 AS DECIMAL(4,3)),' €') AS ideal_ccp
FROM adv_metrics
ORDER BY month, market_segment;
END;
$BODY$ LANGUAGE plpgsql;
SELECT * FROM user_performance('clicks_a', 'cost_a', 'bookings_a', 'booking_rev_a')
Как я могу исправить этот код и заставить его работать?
market_segment
при использовании ее в запросах, например.adv_metrics.market_segment
. В противном случае Postgres(pgplsql) не знает, имеете ли вы в видуmarket_segment
в своей выходной таблице или поле в запросе. - person Adrian Klaver   schedule 17.02.2021