矩阵存储 oracle表,二种矩阵存储方式汇总比较

汇总，也是2快一点，不包括行转列

SQL> create table tm_sum as select

2  rn,

3  sum(c1  )s1  ,

4  sum(c2  )s2  ,

5  sum(c3  )s3  ,

6  sum(c4  )s4  ,

7  sum(c5  )s5  ,

8  sum(c6  )s6  ,

9  sum(c7  )s7  ,

10  sum(c8  )s8  ,

11  sum(c9  )s9  ,

12  sum(c10 )s10 ,

13  sum(c11 )s11 ,

14  sum(c12 )s12 ,

15  sum(c13 )s13 ,

16  sum(c14 )s14 ,

17  sum(c15 )s15 ,

18  sum(c16 )s16 ,

19  sum(c17 )s17 ,

20  sum(c18 )s18 ,

21  sum(c19 )s19 ,

22  sum(c20 )s20 ,

23  sum(c21 )s21 ,

24  sum(c22 )s22 ,

25  sum(c23 )s23 ,

26  sum(c24 )s24 ,

27  sum(c25 )s25 ,

28  sum(c26 )s26 ,

29  sum(c27 )s27 ,

30  sum(c28 )s28 ,

31  sum(c29 )s29 ,

32  sum(c30 )s30 ,

33  sum(c31 )s31 ,

34  sum(c32 )s32 ,

35  sum(c33 )s33 ,

36  sum(c34 )s34 ,

37  sum(c35 )s35 ,

38  sum(c36 )s36 ,

39  sum(c37 )s37 ,

40  sum(c38 )s38 ,

41  sum(c39 )s39 ,

42  sum(c40 )s40 ,

43  sum(c41 )s41 ,

44  sum(c42 )s42 ,

45  sum(c43 )s43 ,

46  sum(c44 )s44 ,

47  sum(c45 )s45 ,

48  sum(c46 )s46 ,

49  sum(c47 )s47 ,

50  sum(c48 )s48 ,

51  sum(c49 )s49 ,

52  sum(c50 )s50 ,

53  sum(c51 )s51 ,

54  sum(c52 )s52 ,

55  sum(c53 )s53 ,

56  sum(c54 )s54 ,

57  sum(c55 )s55 ,

58  sum(c56 )s56 ,

59  sum(c57 )s57 ,

60  sum(c58 )s58 ,

61  sum(c59 )s59 ,

62  sum(c60 )s60 ,

63  sum(c61 )s61 ,

64  sum(c62 )s62 ,

65  sum(c63 )s63 ,

66  sum(c64 )s64 ,

67  sum(c65 )s65 ,

68  sum(c66 )s66 ,

69  sum(c67 )s67 ,

70  sum(c68 )s68 ,

71  sum(c69 )s69 ,

72  sum(c70 )s70 ,

73  sum(c71 )s71 ,

74  sum(c72 )s72 ,

75  sum(c73 )s73 ,

76  sum(c74 )s74 ,

77  sum(c75 )s75 ,

78  sum(c76 )s76 ,

79  sum(c77 )s77 ,

80  sum(c78 )s78 ,

81  sum(c79 )s79 ,

82  sum(c80 )s80 ,

83  sum(c81 )s81 ,

84  sum(c82 )s82 ,

85  sum(c83 )s83 ,

86  sum(c84 )s84 ,

87  sum(c85 )s85 ,

88  sum(c86 )s86 ,

89  sum(c87 )s87 ,

90  sum(c88 )s88 ,

91  sum(c89 )s89 ,

92  sum(c90 )s90 ,

93  sum(c91 )s91 ,

94  sum(c92 )s92 ,

95  sum(c93 )s93 ,

96  sum(c94 )s94 ,

97  sum(c95 )s95 ,

98  sum(c96 )s96 ,

99  sum(c97 )s97 ,

100  sum(c98 )s98 ,

101  sum(c99 )s99 ,

102  sum(c100)s100

103  from tm

104  group by rn;

表已创建。

已用时间:  00: 00: 01.96

SQL> create table tm2_sum as select

2  rn,cn,sum(v)s

3  from tm2

4  group by rn,cn;

表已创建。

已用时间:  00: 00: 00.93