sedang Berlangsung

Write a view in MySQL

I have a table whose DDL is:

CREATE TABLE `Historical_Intraday_Prices` (

`EquityId` int(11) NOT NULL,

`SnapshotDateTime` timestamp NOT NULL,

`Open` double NOT NULL,

`Low` double NOT NULL,

`High` double NOT NULL,

`Close` double NOT NULL,

`Volume` double NOT NULL,

`SnapshotDate` date NOT NULL,

`SnapshotTime` time NOT NULL,

`UpdateToDBTime` timestamp NOT NULL,

`DataSourceName` varchar(50) NOT NULL,

PRIMARY KEY (`EquityId`,`SnapshotDate`,`SnapshotTime`),

KEY `IDX_SNAPSHOTDATE` (`SnapshotDate`),

KEY `IDX_SNAPSHOTDATETIME` (`SnapshotDateTime`),

CONSTRAINT `FKEquityId` FOREIGN KEY (`EquityId`) REFERENCES `EquityMaster` (`EquityId`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1

The snapshotdatetime is a one minute interval. So for a particular EquityId and a particular SnapshotDate, we have per minute data for that day and equity. If the entire market duration is 375 minutes for a particular day, then this table contains 375 records. This means the table stores 1 minute timeframe data for an equity.

The attached file will contain n example. This output is a result of running the below query

SELECT H.* FROM Historical_Intraday_Prices H, EquityMaster E

WHERE E.`EquityId`=H.`EquityId`

AND E.`CompanySymbol`="SBIN"

AND H.`SnapshotDate`='2016-09-01'

I need to create a view so that the output will be a 3 minute timeframe data

Something like running this query:

SELECT H.* FROM Historical_Intraday_Prices_3Timeframe H, EquityMaster E

WHERE E.`EquityId`=H.`EquityId`

AND E.`CompanySymbol`="SBIN"

AND H.`SnapshotDate`='2016-09-01'

This will aggregate the Historical_Intraday_Prices by combining every 3 minute data and then reporting out the same set of columns.

So if the 1 minute timeframe table has 375 rows for a particular equity and particular equity, the output from the view should return 375/3 =125 rows

Calculation:

SnapshotTime should be 9:15, 9:18, 9:21, etc

The open should be the open price at the start of the 3 minute interval

The close should be the close price at the end of the 3 minute interval

The high price should be the high price for the 3 minute duration

The low price should be the low price for the 3 minute duration

Th volume should be the volume sum of all the 3 minutes

The variable inside the view creation script having this timeframe minute which in our case is 3, should be stored as a main variable at the top of the script so that I can change it to 5 minutes if required and then entire process will work for 5 minute timeframe.

Keahlian MySQL

Lihat lebih lanjut: move mysql view, mysql slow write, php mysql view debug, upload photo mysql view, mysql view creator, mysql view, speed mysql view, pdf doc mysql view, java mysql read write

Tentang Pemberi kerja:
( 0 ulasan ) India

ID Proyek: #11722160

Diberikan kepada:

Amritkrsh6

Well Experienced in Mysql development, SQL and PLsql . for further discussion please come to my inbox

₹1300 INR dalam 2 hari
(0 Ulasan)
0.0

2 freelancer menawar pada rata-rata ₹1059 untuk pekerjaan ini

rajesh050

Respected, My name is Rajesh Maurya. We have a highly experienced team for Logo Designing, Server level solutions (i.e. Windows or Linux), Wordpress/ Magento/ Joomla/ Drupal Web development related projects and prob Lagi

₹818 INR dalam 1 hari
(0 Ulasan)
0.0