对冲数据SQL

2013-08-16 03:31:52 473 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

AISMM评估：从成本到风险对冲投资的认知跃迁与实操指南人工智能安全成熟度模型（AISMM）评估是保障AI系统安全的核心框架。其原理在于通过系统化的评估方法，识别AI模型、数据及基础设施中的潜在安全风险与合规差距。这项技术的核心价值在于将传统难以量化的安全投入，转变为可衡量、可管理的风险对冲投资，有效规避因模型攻击、数据泄露或监管不合规导致的重大业务损失。在AI应用快速落地与全球监管趋严的双重背景下，AISMM评估的应用场景已从单纯的技术合规检查，扩展为支撑企业战略决策的关键工具。本文结合行业实践，深入解析如何利用AISMM评估及SITS三年基准对比等工具，将安阅读详情

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
*/

...全文

程序员如何理性面对Bug：从预期断裂到风险对冲 Bug本质上是用户、产品与开发三方预期不一致所引发的信任断裂，而非单纯的代码语法错误。其技术原理根植于系统复杂性、沟通损耗与抽象局限的三重叠加；真正决定其严重性的，是是否造成可感知的业务利益损失。因此，现代工程实践的价值重心正从‘消灭Bug’转向‘驯服Bug’——通过结构化日志实现可观测性前置、借助诊断工具链构建系统认知地图、依托小步迭代完成风险单元化对冲。这些策略共同指向一个更务实的技术价值观：在承认人类认知与系统复杂性存在天然鸿沟的前提下，用可度量、可恢复、可演进的工程手段，持续降低不确定性带来的交付成阅读详情

mugua604

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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 能否详细描述下

对冲数据SQL

相关推荐