Databases
StackOverflow2013
Postsdbo
POSQL - Calculating variable moving average over variable lenghts
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL - Calculating variable moving average over variable lenghts
    primarykey
    data
    text
    <p>FIRST: This question is NOT a duplicate. I have asked this on here already and it was closed as a duplicate. While it is similar to other threads on stackoverflow, it is actually far more complex. Please read the post before assuming it is a duplicate:</p> <p>I am trying to calculate variable moving averages crossover with variable dates.</p> <p>That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit the query based on input or have multiple queries if needed. </p> <pre><code>X = 1st moving average term (N day moving average. Any number 1-N) Y = 2nd moving average term. (N day moving average. Any number 1-N) Z = Amount of days back from present to search for the occurance of: option = Over/Under: (&gt; or &lt;. X passing over Y, or X passing Under Y) X day moving average passing over OR under Y day moving average within the past Z days. </code></pre> <p>My database is structured:</p> <p><strong>tbl_daily_data</strong> </p> <pre><code>id stock_id date adj_close </code></pre> <p>And:</p> <p><strong>tbl_stocks</strong></p> <pre><code>stock_id symbol </code></pre> <p>I have a btree index on: </p> <pre><code>daily_data(stock_id, date, adj_close) stock_id </code></pre> <p>I am stuck on this query and having a lot of trouble writing it. If the variables were fixed it would seem trivial but because X, Y, Z are all 100% independent of each other (could look, for example for 5 day moving average within the past 100 days, or 100 day moving average within the past 5) I am having a lot of trouble coding it.</p> <p>Please help! :(</p> <p>Edit: I've been told some more context might be helpful?</p> <p>We are creating an open stock analytic system where users can perform trend analysis. I have a database containing 3500 stocks and their price histories going back to 1970. </p> <p>This query will be running every day in order to find stocks that match certain criteria for example:</p> <blockquote> <p>10 day moving average crossing over 20 day moving average within 5 days</p> <p>20 day crossing UNDER 10 day moving average within 5 days</p> <p>55 day crossing UNDER 22 day moving average within 100 days</p> </blockquote> <p>But each user may be interested in a different analysis so I cannot just store the moving average with each row, it must be calculated.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1797484
    1. USuser1797484
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL - Calculating variable moving average over variable lenghts
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL - Calculating variable moving average over variable lenghts
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL - Calculating variable moving average over variable lenghts
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAny chance you could post a small data set and the output you want from the query under a couple of set of params?
      singulars
      1. POSQL - Calculating variable moving average over variable lenghts
      1. USIan Kenney
    2. COSure, Coming right up... Sample data: http://zbt-psu.org/daily_data.zip
      singulars
      1. POSQL - Calculating variable moving average over variable lenghts
      1. USuser1797484
    3. COEdited the above question with some parameters. Output = For each/every stock that meets the input parameters: The stock's symbol, The two calculated moving averages and the stock's symbol. Outputting the daily price history for each stocks over the selected period would be nice too but thats a bell and whistle that is not required.
      singulars
      1. POSQL - Calculating variable moving average over variable lenghts
      1. USuser1797484
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload