Write a view in MySQL
₹600-1500 INR
Pago na entrega
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.
ID do Projeto: #11721850
Sobre o projeto
11 freelancers estão ofertando em média ₹1400 nesse trabalho
Hello, I can do the task in less time, and 100% accurate - I'm an experienced software developer specialized in desktop applications, tools, and scripts. - Very good Experience with database based applications (MS- Mais
Hello, I am interested in your project. I am a software engineer and I have professional experience with SQL. Thank you, Best regards, David
Hello, After i read your job description, i'm very suitable and interested for it, I'm Web developer / Designer for more than 5 years, i'm just new here in freelancer but i can assure you the best speed and quality Mais
I have understood your requirement completely, Basically you need an aggregate table/view on top of your existing table. This can be easily achieved using any ETL tools. I'm an Oracle professional having 5 years of exp Mais