sql - 将列转置为行
问题描述
我想将我的宽数据转换为长数据/将列(c_lead 和 mkt_bwon)转置为行
具体来说,我想将列 c_lead 和 mkt_bwon 转换为行,其中实际值、目标和差异各有 c_lead 和 mkt_bwon 等行。
因此,每个“row_names”的单独行标记为 mkt_bwon。
select
'actuals' as row_name,
cast(period_to as datetime) as time_period,
--determinant,
--sale_location,
--region,
coalesce(leads,0) as leads,
coalesce(opportunities,0) as opps,
coalesce(b_won,0) as bwon,
coalesce(cast(total_spend as decimal(18,2)),0) as t_spend,
coalesce(discount_total,0) as disc,
(t_spend+disc) as t_cost,
(t_spend/leads) as c_lead,
(t_spend/bwon) as mkt_bwon
from
periscope_views.salesforce_deepdive
where
determinant = 'online'
union all
select 'targets' as row_name,
date as time_period,
coalesce(leads, 0) as leads,
coalesce(opps, 0) as opps,
coalesce(won, 0) as bwon,
coalesce(spend, 0) as t_spend,
coalesce(discount, 0) as t_disc,
(t_spend+t_disc) as t_cost,
(t_spend/leads) as c_lead,
(t_spend/bwon) as mkt_bwon
from periscope_views.targets_csv2019
where source = 'online'
union all
select
'difference',
time_period,
sum(leads) as leads,
sum(opps) as opps,
sum(bwon) as bwon,
sum(t_spend) as t_spend,
sum(disc) as disc,
sum(total_cost) as t_cost,
sum(c_lead) as c_lead,
sum(mkt_bwon) as mkt_bwon
from
(
select
'actuals' as row_name,
cast(period_to as datetime) as time_period,
--determinant,
--sale_location,
--region,
coalesce(leads,0) as leads,
coalesce(opportunities,0) as opps,
coalesce(b_won,0) as bwon,
coalesce(cast(total_spend as decimal(18,2)),0) as t_spend,
coalesce(discount_total,0) as disc,
(total_spend+discount_total) as total_cost,
(t_spend/leads) as c_lead,
(t_spend/bwon) as mkt_bwon
from
periscope_views.salesforce_deepdive
where
determinant = 'online'
union all
select 'targets' as row_name,
date as time_period,
coalesce(leads, 0)*-1 as leads,
coalesce(opps, 0)*-1 as opps,
coalesce(won, 0)*-1 as bwon,
coalesce(spend, 0)*-1 as t_spend,
coalesce(discount, 0)*-1 as t_disc,
(t_spend+t_disc)*-1 as t_cost,
(t_spend/leads)*-1 as c_lead,
(t_spend/bwon)*-1 as mkt_bwon
from periscope_views.targets_csv2019
where source = 'online'
)
group by time_period,row_name
order by time_period desc
差异部分应该简单地计算实际值和目标值之间的差异
解决方案
推荐阅读
- javascript - 在不改变 r 值的情况下改变气泡半径的大小
- javascript - 组件无法从 React Native 中的 index.d.ts 文件自动导入类型
- flutter - 解决了 Flutter - Infinite Scroll & Images 导致 App Crash
- laravel - 在 laravel 7 中使用 gmail 发送邮件
- r - 闪亮:updateSelectInput() 选择的参数问题与observe()
- python - Python 发送带有相应附件的电子邮件
- css-selectors - 富结果测试在架构中为面包屑返回“null”和“未命名”,但它在 Google 结构化数据测试工具中正确显示
- android - 使用 InApp 更新 API 模式 灵活
- kotlin - 在没有 val 或 var 的 Kotlin 中解构返回
- c - 为什么我的 cs50 中的 valid_triangle 程序不起作用?