Closed

Write a view in MySQL

This project received 12 bids from talented freelancers with an average bid price of ₹1429 INR.

Get free quotes for a project like this
Employer working
Project Budget
₹600 - ₹1500 INR
Total Bids
12
Project Description

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.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online