mysql视图分区处理,在MySQL中创建视图或分区示例
Hello All,
This is Regards of creating a View of Additional column with following requirement.
It would be great if I get some information on this
Example
I have a Table with the column as Specified and Sample value as below.TABLENAME
RowNumber UtcTimeStamp TotalRequest
1 2015-03-04 12:20:00 10441856
2 2015-03-04 12:30:00 10447205
3 2015-03-04 12:40:00 10454634
4 2015-03-04 12:50:00 10461790
5 2015-03-04 13:00:00 10469014
6 2015-03-04 13:10:00 10476494
I would need to create a View as following or PartitionLike below
Like always the First Row "ColumnResult" Column is 0
"ColumnResult" of Row 2 is
And from Row 2 Onwards, "ColumnResult"is =
TotalRequest(Value in Row 2 ) - TotalRequest(Value in Row 1 )
"ColumnResult" of Row 3 is
TotalRequest(Value in Row 3 ) - TotalRequest(Value in Row 2 )
and So on..
RowNumber UtcTimeStamp TotalRequest ColumnResult
1 2015-03-04 12:20:00 10441856 0
2 2015-03-04 12:30:00 10447205 5349
3 2015-03-04 12:40:00 10454634 7429
4 2015-03-04 12:50:00 10461790 7156
5 2015-03-04 13:00:00 10469014 ...
6 2015-03-04 13:10:00 10476494 so on...
Thanks and Regards
Durai Velan C.
解决方案Hello Team,
I am closing this case, as I have got solution for it.
=======================================================
CREATE PROCEDURE GetDiffView()
BEGIN
SET @isFirstRow=1;
SET @ColumnThreeValuePrevious=0;
SELECT ColumnOne, ColumnTwo, ColumnThree
FROM (
SELECT ColumnOne, ColumnTwo.
@VColumnThree:=ColumnThree as CColumnThree,
(CASE
WHEN @isFirstRow=1 THEN 0
ELSE @VColumnThree-@ColumnThreeValuePrevious
END) as ColumnThree,
@ColumnThreeValuePrevious:=@VColumnThree,
@isFirstRow:=0
FROM DBTABLE ) Diff
END
Thanks and Regards
Durai Velan C
Hello All, This is Regards of creating a View of Additional column with following requirement. It would be great if I get some information on this Example I have a Table with the column as Specified and Sample value as below.TABLENAME RowNumber UtcTimeStamp TotalRequest 1 2015-03-04 12:20:00 10441856 2 2015-03-04 12:30:00 10447205 3 2015-03-04 12:40:00 10454634 4 2015-03-04 12:50:00 10461790 5 2015-03-04 13:00:00 10469014 6 2015-03-04 13:10:00 10476494 I would need to create a View as following or PartitionLike below Like always the First Row "ColumnResult" Column is 0 "ColumnResult" of Row 2 is And from Row 2 Onwards, "ColumnResult"is = TotalRequest(Value in Row 2 ) - TotalRequest(Value in Row 1 ) "ColumnResult" of Row 3 is TotalRequest(Value in Row 3 ) - TotalRequest(Value in Row 2 ) and So on.. RowNumber UtcTimeStamp TotalRequest ColumnResult 1 2015-03-04 12:20:00 10441856 0 2 2015-03-04 12:30:00 10447205 5349 3 2015-03-04 12:40:00 10454634 7429 4 2015-03-04 12:50:00 10461790 7156 5 2015-03-04 13:00:00 10469014 ... 6 2015-03-04 13:10:00 10476494 so on... Thanks and Regards Durai Velan C. 解决方案Hello Team, I am closing this case, as I have got solution for it. ======================================================= CREATE PROCEDURE GetDiffView() BEGIN SET @isFirstRow=1; SET @ColumnThreeValuePrevious=0; SELECT ColumnOne, ColumnTwo, ColumnThree FROM ( SELECT ColumnOne, ColumnTwo. @VColumnThree:=ColumnThree as CColumnThree, (CASE WHEN @isFirstRow=1 THEN 0 ELSE @VColumnThree-@ColumnThreeValuePrevious END) as ColumnThree, @ColumnThreeValuePrevious:=@VColumnThree, @isFirstRow:=0 FROM DBTABLE ) Diff END Thanks and Regards Durai Velan C