对冲数据SQL

mugua604 2013-08-16 03:31:52 

CREATE TABLE TEMP_01(ID NVARCHAR(10),DAT NVARCHAR(10),Qty DECIMAL(22,2))

INSERT INTO TEMP_01

SELECT '100101','2013/07/18',700.00

UNION ALL

SELECT '100101','2013/07/19',800.00

UNION ALL

SELECT '100101','2013/07/24',813.00

UNION ALL

SELECT '100101','2013/07/25',10000.00

UNION ALL

SELECT '100101','2013/07/28',19743.00

UNION ALL

SELECT '100101','2013/07/30',9300.00

UNION ALL

SELECT '100102','2013/07/18',700.00

UNION ALL

SELECT '100102','2013/07/19',800.00

UNION ALL

SELECT '100102','2013/07/25',10000.00

UNION ALL

SELECT '100102','2013/07/30',9300.00





CREATE TABLE TEMP_02(ID NVARCHAR(10),Qty DECIMAL(22,2))

INSERT INTO TEMP_02

SELECT '100101',24610.00

UNION ALL

SELECT '100101',24610.00


想要得到以下结果，如果TEMP_02的值大于了TEMP_01的总值，日期可取当天日期
/*
100101 2013/07/18 700.00 700
100101 2013/07/19 800.00 800
100101 2013/07/24 813.00 813
100101 2013/07/25 10000.00 10000
100101 2013/07/28 19743.00 12297
100101 2013/07/30 9300.00 0

100102 2013/07/18 700.00 700
100102 2013/07/19 800.00 800
100102 2013/07/25 10000.00 10000
100102 2013/07/30 9300.00 9300
100102 2013/08/16 -3810.00 3810
*/
...全文
401 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
com8716187161 2016-03-14
  • 打赏
  • 举报
 回复
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 IF OBJECT_ID('tempdb..#TEMP_01') IS NOT NULL DROP TABLE #TEMP_01 go IF OBJECT_ID('tempdb..#TEMP_02') IS NOT NULL DROP TABLE #TEMP_02 go CREATE TABLE #TEMP_01(ID NVARCHAR(10),DAT NVARCHAR(10),Qty DECIMAL(22,2)) INSERT INTO #TEMP_01 SELECT '100101','2013/07/18',700.00 UNION ALL SELECT '100101','2013/07/19',800.00 UNION ALL SELECT '100101','2013/07/24',813.00 UNION ALL SELECT '100101','2013/07/25',10000.00 UNION ALL SELECT '100101','2013/07/28',19743.00 UNION ALL SELECT '100101','2013/07/30',9300.00 UNION AL LUNION ALL SELECT '100102','2013/07/18',700.00 UNION ALL SELECT '100102','2013/07/19',800.00 UNION UNION ALL ALL SELECT '100102','2013/07/25',10000.00 UNION www.60400.com ALL SELECT '100102','2013/07/30',9300.00 UNION ALLU NIO N ALL SELECT '100103','2013/07/30',24600.00
qq_34163821 2016-03-11
  • 打赏
  • 举报
 回复
一组数据库。以三维几何分析，单位(行)，分比如:乐观值,可能值,悲观值,期望值。 这时，如果我想提取平均值后，做单项数值库的数列对比的测试分析。，是否提取总库平均值后，以横向来逐渐分别消减三项的首项横向数据库，还是以纵向来逐渐消减三项的
Shawn 2013-08-16
  • 打赏
  • 举报
 回复
引用 4 楼 mugua604 的回复:
如果多个产品不够扣如何修改
--出了点儿BUG，修改一下： 
IF OBJECT_ID('tempdb..#TEMP_01') IS NOT NULL DROP TABLE #TEMP_01
go
IF OBJECT_ID('tempdb..#TEMP_02') IS NOT NULL DROP TABLE #TEMP_02
go
CREATE TABLE #TEMP_01(ID NVARCHAR(10),DAT NVARCHAR(10),Qty DECIMAL(22,2))
INSERT INTO #TEMP_01
SELECT '100101','2013/07/18',700.00
UNION ALL
SELECT '100101','2013/07/19',800.00
UNION ALL
SELECT '100101','2013/07/24',813.00
UNION ALL
SELECT '100101','2013/07/25',10000.00
UNION ALL
SELECT '100101','2013/07/28',19743.00
UNION ALL
SELECT '100101','2013/07/30',9300.00
UNION ALL
SELECT '100102','2013/07/18',700.00
UNION ALL
SELECT '100102','2013/07/19',800.00
UNION ALL
SELECT '100102','2013/07/25',10000.00
UNION ALL
SELECT '100102','2013/07/30',9300.00
UNION ALL
SELECT '100103','2013/07/30',24600.00
  
  
CREATE TABLE #TEMP_02(ID NVARCHAR(10),Qty DECIMAL(22,2))
INSERT INTO #TEMP_02
SELECT '100101',24610.00
UNION ALL
SELECT '100102',24610.00
UNION ALL
SELECT '100103',24610.00
 
;WITH cte AS
(
    SELECT *, 
        sumQty=(SELECT SUM(qty) FROM #TEMP_01 b WHERE b.id=a.id AND b.dat<=a.dat),
        total = (SELECT TOP(1) qty FROM #TEMP_02 b WHERE b.id = a.id),
        rowid = ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY Dat)
    FROM #TEMP_01 a
),
cte1 AS
(
    SELECT *,diff=total-sumQty FROM cte
)
SELECT 
    id, dat, qty, newqty=(CASE WHEN diff>0 THEN Qty WHEN (NOT EXISTS(SELECT 1 FROM CTE1 m WHERE m.id=N.id AND m.dat<n.dat AND m.diff<0)) THEN Qty+diff ELSE 0 end)
FROM cte1 N
UNION ALL
SELECT c.* FROM
(
    SELECT DISTINCT X.ID
    FROM cte1 X
    WHERE NOT EXISTS
    (
        SELECT 1 FROM cte1 Y
        WHERE y.id = x.id
        AND diff < 0
    )
) B
CROSS APPLY
(
	SELECT TOP(1) id, dat=CONVERT(CHAR(10),GETDATE(),111), Qty=-diff, neweQty=diff
	FROM cte1 z
	WHERE z.id = B.id
	ORDER BY z.rowid DESC
) c
ORDER BY id, dat
/*
id	dat	qty	newqty
100101	2013/07/18	700.00	700.00
100101	2013/07/19	800.00	800.00
100101	2013/07/24	813.00	813.00
100101	2013/07/25	10000.00	10000.00
100101	2013/07/28	19743.00	12297.00
100101	2013/07/30	9300.00	0.00
100102	2013/07/18	700.00	700.00
100102	2013/07/19	800.00	800.00
100102	2013/07/25	10000.00	10000.00
100102	2013/07/30	9300.00	9300.00
100102	2013/08/16	-3810.00	3810.00
100103	2013/07/30	24600.00	24600.00
100103	2013/08/16	-10.00	10.00
*/
mugua604 2013-08-16
  • 打赏
  • 举报
 回复
如果多个产品不够扣如何修改
Shawn 2013-08-16
  • 打赏
  • 举报
 回复 
IF OBJECT_ID('tempdb..#TEMP_01') IS NOT NULL DROP TABLE #TEMP_01
go
IF OBJECT_ID('tempdb..#TEMP_02') IS NOT NULL DROP TABLE #TEMP_02
go
CREATE TABLE #TEMP_01(ID NVARCHAR(10),DAT NVARCHAR(10),Qty DECIMAL(22,2))
INSERT INTO #TEMP_01
SELECT '100101','2013/07/18',700.00
UNION ALL
SELECT '100101','2013/07/19',800.00
UNION ALL
SELECT '100101','2013/07/24',813.00
UNION ALL
SELECT '100101','2013/07/25',10000.00
UNION ALL
SELECT '100101','2013/07/28',19743.00
UNION ALL
SELECT '100101','2013/07/30',9300.00
UNION ALL
SELECT '100102','2013/07/18',700.00
UNION ALL
SELECT '100102','2013/07/19',800.00
UNION ALL
SELECT '100102','2013/07/25',10000.00
UNION ALL
SELECT '100102','2013/07/30',9300.00
 
 
CREATE TABLE #TEMP_02(ID NVARCHAR(10),Qty DECIMAL(22,2))
INSERT INTO #TEMP_02
SELECT '100101',24610.00
UNION ALL
SELECT '100102',24610.00

;WITH cte AS
(
	SELECT *, 
		sumQty=(SELECT SUM(qty) FROM #TEMP_01 b WHERE b.id=a.id AND b.dat<=a.dat),
		total = (SELECT TOP(1) qty FROM #TEMP_02 b WHERE b.id = a.id),
		rowid = ROW_NUMBER() OVER(PARTITION BY a.id ORDER BY Dat)
	FROM #TEMP_01 a
),
cte1 AS
(
	SELECT *,diff=total-sumQty FROM cte
)
SELECT 
	id, dat, qty, newqty=(CASE WHEN diff>0 THEN Qty WHEN (NOT EXISTS(SELECT 1 FROM CTE1 m WHERE m.id=N.id AND m.dat<n.dat AND m.diff<0)) THEN Qty+diff ELSE 0 end)
FROM cte1 N
UNION ALL
SELECT * FROM
(
	SELECT TOP(1) id, dat=CONVERT(CHAR(10),GETDATE(),111), Qty=-diff, neweQty=diff
	FROM cte1 X
	WHERE NOT EXISTS
	(
		SELECT 1 FROM cte1 Y
		WHERE y.id = x.id
		AND diff < 0
	)
	ORDER BY X.rowid DESC
) B
ORDER BY id, dat
/*
id	dat	qty	newqty
100101	2013/07/18	700.00	700.00
100101	2013/07/19	800.00	800.00
100101	2013/07/24	813.00	813.00
100101	2013/07/25	10000.00	10000.00
100101	2013/07/28	19743.00	12297.00
100101	2013/07/30	9300.00	0.00
100102	2013/07/18	700.00	700.00
100102	2013/07/19	800.00	800.00
100102	2013/07/25	10000.00	10000.00
100102	2013/07/30	9300.00	9300.00
100102	2013/08/16	-3810.00	3810.00
*/
mugua604 2013-08-16
  • 打赏
  • 举报
 回复
-3810.00 这个是不够冲了，将差额部分以负数体现
开着拖拉机泡妞 2013-08-16
  • 打赏
  • 举报
 回复
给的测试数据是否有问题？？ 100102 2013/08/16 -3810.00 3810 能否详细描述下
智慧企业大数据平台建设综合解决方案.pptx
智慧企业大数据平台建设综合解决方案
沪深A股MATLAB应用程序包说明书
很好的金融计算参考资料，matlab参考手册
大数据解决方案V1.0.pptx
大数据解决方案V1.0
大数据解决方案(PPT 44页).pptx
大数据解决方案(PPT 44页).pptx
大数据平台建设方法解决方案.pptx
大数据平台建设方案
疑难问题

22,300

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章