关键词：
【中文标题】使用数据透视的复杂 SQL 查询【英文标题】：Complex SQL query with pivot 【发布时间】：2015-04-08 04:54:44 【问题描述】：我有下表。
数据表
R_id I_id 度量 CType 时间跨度 数量 日期 1 1 S C 第 100 周 2015 年 4 月 5 日 1 1 Q C 第 200 周 2015 年 4 月 5 日 1 1 I D 第 80 周 2015 年 4 月 5 日 1 2 S C 第 150 周 2015 年 4 月 5 日 1 2 Q C 第 100 周 2015 年 4 月 5 日 1 2 I D 第 50 周 2015 年 4 月 5 日 指标可以有一组有限的值（S、Q、I..） CType 将为 C、D 或 nil。 时间跨度可以是每周/每天。 日期将是每周的星期日（一周开始）和每日的当天日期。我的目标是将其转换为日常视图，这将涉及
-
如果 Timespan 为 Daily，请照原样复制上述指标的 Quantity。
将每周数量转换为每天 7 个数量。
如果 CType 为 D，则按原样复制数量。 如果 CType 是 C，则使用恒定百分比细分逻辑在 7 天内分配每周。例如 [30%, 10%, 10%, 5%, 10%, 15% 20%] = 100%创建以下视图。
R_id I_id Date S Q I ...（其他 CType 不为 nil 的指标） 1 1 4/5/2015 30 60 80 ...（其他指标的数量） 1 1 2015 年 4 月 6 日 10 20 80 1 1 2015 年 4 月 7 日 10 20 80 1 1 2015 年 4 月 8 日 5 10 80 1 1 2015 年 4 月 9 日 10 20 80 1 1 2015 年 4 月 10 日 15 30 80 1 1 2015 年 4 月 11 日 20 40 80 1 2 2015 年 4 月 5 日 45 30 50 1 2 2015 年 4 月 6 日 15 10 50 1 2 2015 年 4 月 7 日 15 10 50 1 2 2015 年 4 月 8 日 7.5 5 50 1 2 2015 年 4 月 9 日 15 10 50 1 2 2015 年 4 月 10 日 22.5 15 50 1 2 2015 年 4 月 11 日 30 20 50我可以编写一堆 java 方法，这些方法将从上表中提取数据并根据需要获取指标的值。但是对于大型数据集，性能不会很好。数据库适用于这种类型的数据计算。一旦创建了这个视图，我就可以快速（简单地）查询它以获得我想要的。我可以编写简单的 sql 查询。但我不知道如何开始解决这个问题！我可以在这里看到一个 PIVOT（从逻辑上讲，我不知道查询将如何甚至可以实现它）。但是如何从每周的数量中计算出 7 天的数量并将其放入 VIEW 中？
我们将不胜感激建议和指导。
【问题讨论】：
更多样本数据和预期输出会很好。或者，更好的 sqlfiddle @EatÅPeach - 希望这能更好地解释问题。 【参考方案1】：您可以使用hierarchical 查询来生成每日数据。
SQL Fiddle
查询：
select
r_id,
i_id,
metric,
ctype,
timespan,
quantity,
tdate + level - 1 as m_tdate,
level as m_level,
(case ctype
when 'C' then
(case level
when 1 then 0.3
when 2 then 0.1
when 3 then 0.1
when 4 then 0.05
when 5 then 0.1
when 6 then 0.15
when 7 then 0.2
end)
else 1
end) * quantity as m_quantity
from myt
where timespan = 'Week'
connect by level <= 7
and r_id = prior r_id
and i_id = prior i_id
and metric = prior metric
and ctype = prior ctype
and timespan = prior timespan
and prior sys_guid() is not null
这将为每条记录生成 7 天的数据
Results：
| R_ID | I_ID | METRIC | CTYPE | TIMESPAN | QUANTITY | M_TDATE | M_LEVEL | M_QUANTITY |
|------|------|--------|-------|----------|----------|-----------------------|---------|------------|
| 1 | 1 | I | D | Week | 80 | May, 04 2015 00:00:00 | 1 | 80 |
| 1 | 1 | I | D | Week | 80 | May, 05 2015 00:00:00 | 2 | 80 |
| 1 | 1 | I | D | Week | 80 | May, 06 2015 00:00:00 | 3 | 80 |
| 1 | 1 | I | D | Week | 80 | May, 07 2015 00:00:00 | 4 | 80 |
| 1 | 1 | I | D | Week | 80 | May, 08 2015 00:00:00 | 5 | 80 |
| 1 | 1 | I | D | Week | 80 | May, 09 2015 00:00:00 | 6 | 80 |
| 1 | 1 | I | D | Week | 80 | May, 10 2015 00:00:00 | 7 | 80 |
| 1 | 1 | Q | C | Week | 200 | May, 04 2015 00:00:00 | 1 | 60 |
| 1 | 1 | Q | C | Week | 200 | May, 05 2015 00:00:00 | 2 | 20 |
| 1 | 1 | Q | C | Week | 200 | May, 06 2015 00:00:00 | 3 | 20 |
| 1 | 1 | Q | C | Week | 200 | May, 07 2015 00:00:00 | 4 | 10 |
| 1 | 1 | Q | C | Week | 200 | May, 08 2015 00:00:00 | 5 | 20 |
| 1 | 1 | Q | C | Week | 200 | May, 09 2015 00:00:00 | 6 | 30 |
| 1 | 1 | Q | C | Week | 200 | May, 10 2015 00:00:00 | 7 | 40 |
| 1 | 1 | S | C | Week | 100 | May, 04 2015 00:00:00 | 1 | 30 |
| 1 | 1 | S | C | Week | 100 | May, 05 2015 00:00:00 | 2 | 10 |
| 1 | 1 | S | C | Week | 100 | May, 06 2015 00:00:00 | 3 | 10 |
| 1 | 1 | S | C | Week | 100 | May, 07 2015 00:00:00 | 4 | 5 |
| 1 | 1 | S | C | Week | 100 | May, 08 2015 00:00:00 | 5 | 10 |
| 1 | 1 | S | C | Week | 100 | May, 09 2015 00:00:00 | 6 | 15 |
| 1 | 1 | S | C | Week | 100 | May, 10 2015 00:00:00 | 7 | 20 |
| 1 | 2 | I | D | Week | 50 | May, 04 2015 00:00:00 | 1 | 50 |
| 1 | 2 | I | D | Week | 50 | May, 05 2015 00:00:00 | 2 | 50 |
| 1 | 2 | I | D | Week | 50 | May, 06 2015 00:00:00 | 3 | 50 |
| 1 | 2 | I | D | Week | 50 | May, 07 2015 00:00:00 | 4 | 50 |
| 1 | 2 | I | D | Week | 50 | May, 08 2015 00:00:00 | 5 | 50 |
| 1 | 2 | I | D | Week | 50 | May, 09 2015 00:00:00 | 6 | 50 |
| 1 | 2 | I | D | Week | 50 | May, 10 2015 00:00:00 | 7 | 50 |
| 1 | 2 | Q | C | Week | 100 | May, 04 2015 00:00:00 | 1 | 30 |
| 1 | 2 | Q | C | Week | 100 | May, 05 2015 00:00:00 | 2 | 10 |
| 1 | 2 | Q | C | Week | 100 | May, 06 2015 00:00:00 | 3 | 10 |
| 1 | 2 | Q | C | Week | 100 | May, 07 2015 00:00:00 | 4 | 5 |
| 1 | 2 | Q | C | Week | 100 | May, 08 2015 00:00:00 | 5 | 10 |
| 1 | 2 | Q | C | Week | 100 | May, 09 2015 00:00:00 | 6 | 15 |
| 1 | 2 | Q | C | Week | 100 | May, 10 2015 00:00:00 | 7 | 20 |
| 1 | 2 | S | C | Week | 150 | May, 04 2015 00:00:00 | 1 | 45 |
| 1 | 2 | S | C | Week | 150 | May, 05 2015 00:00:00 | 2 | 15 |
| 1 | 2 | S | C | Week | 150 | May, 06 2015 00:00:00 | 3 | 15 |
| 1 | 2 | S | C | Week | 150 | May, 07 2015 00:00:00 | 4 | 7.5 |
| 1 | 2 | S | C | Week | 150 | May, 08 2015 00:00:00 | 5 | 15 |
| 1 | 2 | S | C | Week | 150 | May, 09 2015 00:00:00 | 6 | 22.5 |
| 1 | 2 | S | C | Week | 150 | May, 10 2015 00:00:00 | 7 | 30 |
一旦你有了这个，你需要旋转结果，这可以通过简单的 GROUP BY 来完成
查询：
with x as (
select
r_id,
i_id,
metric,
ctype,
timespan,
quantity,
tdate + level - 1 as m_tdate,
level as m_level,
(case ctype
when 'C' then
(case level
when 1 then 0.3
when 2 then 0.1
when 3 then 0.1
when 4 then 0.05
when 5 then 0.1
when 6 then 0.15
when 7 then 0.2
end)
else 1
end) * quantity as m_quantity
from myt
where timespan = 'Week'
connect by level <= 7
and r_id = prior r_id
and i_id = prior i_id
and metric = prior metric
and ctype = prior ctype
and timespan = prior timespan
and prior sys_guid() is not null
UNION ALL
select
r_id,
i_id,
metric,
ctype,
timespan,
quantity,
tdate as m_tdate,
1 as m_level,
quantity as m_quantity
from myt
where timespan = 'Day'
)
select
r_id,
i_id,
m_tdate,
sum(case when metric = 'S' then m_quantity end) S,
sum(case when metric = 'Q' then m_quantity end) Q,
sum(case when metric = 'I' then m_quantity end) I
from x
group by
r_id,
i_id,
m_tdate
order by
r_id,
i_id,
m_tdate
Results：
| R_ID | I_ID | M_TDATE | S | Q | I |
|------|------|-------------------------|--------|--------|-----|
| 1 | 1 | May, 04 2015 00:00:00 | 30 | 60 | 80 |
| 1 | 1 | May, 05 2015 00:00:00 | 10 | 20 | 80 |
| 1 | 1 | May, 06 2015 00:00:00 | 10 | 20 | 80 |
| 1 | 1 | May, 07 2015 00:00:00 | 5 | 10 | 80 |
| 1 | 1 | May, 08 2015 00:00:00 | 10 | 20 | 80 |
| 1 | 1 | May, 09 2015 00:00:00 | 15 | 30 | 80 |
| 1 | 1 | May, 10 2015 00:00:00 | 20 | 40 | 80 |
| 1 | 2 | April, 03 2015 00:00:00 | (null) | (null) | 120 |
| 1 | 2 | May, 04 2015 00:00:00 | 45 | 30 | 50 |
| 1 | 2 | May, 05 2015 00:00:00 | 15 | 10 | 50 |
| 1 | 2 | May, 06 2015 00:00:00 | 15 | 10 | 50 |
| 1 | 2 | May, 07 2015 00:00:00 | 7.5 | 5 | 50 |
| 1 | 2 | May, 08 2015 00:00:00 | 15 | 10 | 50 |
| 1 | 2 | May, 09 2015 00:00:00 | 22.5 | 15 | 50 |
| 1 | 2 | May, 10 2015 00:00:00 | 30 | 20 | 50 |
【讨论】：
使用 Sql Developer Oracle 的动态数据透视查询
】使用SqlDeveloperOracle的动态数据透视查询【英文标题】：DynamicPivotQueryusingSqlDeveloperOracle【发布时间】：2020-09-0119:46:04【问题描述】：初始表学生NAMESchoolClassJohnHsEnglishSteveHsMathsMatthewHsScienceJimHsHistory需要输出：我需要查询从初始表... 查看详情
使用子查询 oracle sql 进行数据透视
】使用子查询oraclesql进行数据透视【英文标题】：Pivotwithsubqueryoraclesql【发布时间】：2014-09-2504:24:44【问题描述】：我正在尝试使用pivot来显示我的信息。我想使用子查询来选择报告中的列：select*from(selectcontinent,country,populationfrom... 查看详情
SQL Server 中的复杂数据透视表
】SQLServer中的复杂数据透视表【英文标题】：ComplexPivottableinSQLServer【发布时间】：2021-07-1417:53:20【问题描述】：我有一些看起来像这样的数据：SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE[dbo].[tblMyLog]([Pkid][bigint]IDENTITY(1,1)NOTNULL,[JobId... 查看详情
SQL Server 中的数据透视表查询
...要获取与每个不同元素的最大日期时间戳相对应的值，并使用SQLServer2005或更高版本将行转换为列。列名值（即A1、A2、A3列是动态的，可以根据可用数据进行更改）基表（来自结果查询）IdNameValueDateTimeStamp------ 查看详情
在 LibreOffice Base 中创建等效数据透视表的 SQL (HSQLDB) 查询
...se中的数据库，并尝试输出相当于数据透视表的内容。Base使用HSQL，据我所知，它不支持数据透视，但您可以使用它制作的 查看详情
用于从 VIEW 表中提取数据的 SQL 透视查询
...omaVIEWTable【发布时间】：2014-12-2306:46:38【问题描述】：我使用的是SQLServer2014，并且我有以下查询（我已将其转换为VIEW表）：SELECTb.PropertyCode,c.PMSConfirmationNumber,x.[ReservationStayID],a.FirstNam 查看详情
转置列或反透视 SQL 查询
...前正在尝试在SQL查询中转置一些数据，但是我似乎找不到使用un-pivot的解决方案。我正在使用的数据示例是SELECT*FROM(SELECT\'ACCOUNTS\'ASDept,DATENAME(MONTH,GETDATE())AS[Month],\'3254\ 查看详情
如何使用 php 数组将 sql 查询结果转换为数据透视表？
】如何使用php数组将sql查询结果转换为数据透视表？【英文标题】：Howtotransformsqlqueryresultstopivottableusingphparrays?【发布时间】：2015-02-2512:55:24【问题描述】：我正在尝试为图书馆预订系统创建一些统计数据。我的sql查询结果如下... 查看详情
Oracle SQL 数据透视查询
】OracleSQL数据透视查询【英文标题】：OracleSQLpivotquery【发布时间】：2011-06-1800:32:50【问题描述】：我的表格中有如下数据：MONTHVALUE110022003300440055006600我想写一个SQL查询，结果如下：MONTH_JANMONTH_FEBMONTH_MARMONTH_APRMONTH_MAYMONTH_JUN1002003... 查看详情
Oracle SQL 数据透视查询
】OracleSQL数据透视查询【英文标题】：OracleSQLpivotquery【发布时间】：2011-01-3007:29:41【问题描述】：我的表格中有如下数据：MONTHVALUE110022003300440055006600我想写一个SQL查询，结果如下：MONTH_JANMONTH_FEBMONTH_MARMONTH_APRMONTH_MAYMONTH_JUN1002003... 查看详情
Sql Server 聚合或数据透视表查询
】SqlServer聚合或数据透视表查询【英文标题】：SqlServerAggregationorPivotTableQuery【发布时间】：2015-10-1407:32:25【问题描述】：我正在尝试编写一个查询，该查询将告诉我每周进行一定数量交易的客户数量。我不知道从哪里开始查询... 查看详情
没有聚合函数的 SQL Server 数据透视查询
】没有聚合函数的SQLServer数据透视查询【英文标题】：SQLServerpivotquerywithoutaggregatefunction【发布时间】：2019-12-1713:01:03【问题描述】：我有一张表Products，它存储主产品信息。此表中的列是：Products========ProductNoColorSizeBrandPriceABLKSMS... 查看详情
从 sql 查询中透视和合并数据
】从sql查询中透视和合并数据【英文标题】：Pivotingandmergingdatafromasqlquery【发布时间】：2011-06-1320:46:13【问题描述】：这是我的查询：SELECTad.Name,av.ValueASAttribute1,ctv.ValueASAttribute2FROMAttributeDefinitionsASadWITH(nolock)INNERJOINAttributeValuesASav 查看详情
SQL查询将2个表中的数据与数据透视组合
】SQL查询将2个表中的数据与数据透视组合【英文标题】：SQLquerytocombinedatafrom2tableswithpivot【发布时间】：2017-11-1603:29:55【问题描述】：我有2张桌子：CREATETABLE[ObIndex]([idx][int]IDENTITY(1,1)NOTNULL,[dtg][smalldatetime]NOTNULL,[site][varchar](6)NOTNUL... 查看详情
SQL Server 查询侧重于“透视”包含非数字数据的表
】SQLServer查询侧重于“透视”包含非数字数据的表【英文标题】：SQLServerqueryfocusingon"pivoting"atablecontainingNon-numericdata【发布时间】：2019-06-1809:42:59【问题描述】：表格名称：职业**NameOccupation**SamanthaDoctorJuliaActorMariaActorMeera... 查看详情
每月汇总金额的 SQL 数据透视表
...1-1321:18:15【问题描述】：我在sql方面没有太多经验。在我使用ucanaccess库的小型Java程序中，我能够在创建一个简单的表（名为ReportSales）后执行一些简单的查询。表格是：IDDATEPRODUCTSALESFEES12014-10-02productA10.0 查看详情
SQL Server 数据透视查询 - 问题
】SQLServer数据透视查询-问题【英文标题】：SQLServerpivotquery-questions【发布时间】：2020-11-2123:52:25【问题描述】：我有一个包含3列的表格：order_id,product_id,product_count第一列是客户传递的订单，第二列是产品唯一ID，第三列是订单中... 查看详情
在 Oracle SQL 中将数据透视表转换为平面表
...时间】：2013-09-2515:47:08【问题描述】：考虑下表：我正在使用下面的查询（在SQLServer中）将此表转换为平面表，如下所示：我想用OracleSQL做同样的事情。但是，该查询在“OracleSQL”语言中不起作用：crossapply，下面使用的，在Oracl... 查看详情