Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You wouldn't (as such) have to keep the date of every view. You could instead keep up to 366 rows per item in a table with columns: product_id, day_of_year, count. Each day run a task to zero all the counts from one year ago. If you don't mind denormalised data, that task could also update the "count" field in the item itself for fast retrieval, so that your query does not need to change. <code>product_views</code> just becomes <code>product_views_in_the_last_year</code>. The time period of 1 day is arbitrary - I doubt you care that the popularity is based on a window of precisely 1 year, so I expect it could just as well be an hour, a week or a fortnight, depending how many buckets you're willing to deal with.</p> <p>An alternative scheme might be to use exponential decay. Turn the count field into a decimal type. Once per day, reduce the count of each item by a fixed percentage (less than 1%, more than 0.1%), so that the more recent a hit is, the more "weight" it has. So old popularity never dies entirely, but hits from a year ago won't contribute much. An equivalent to this scheme, by the way, is to leave the code as it is but ensure that your website as a whole becomes exponentially more popular over time ;-)</p> <p>As for avoiding starting from zero - maybe reduce each item's count immediately, as a one-off action, by a proportion which depends on the age of the item. On the whole you'd expect that older objects have older views, and hence are over-rated by the current scheme. That's not fool-proof, since maybe some older items have recently gained a lot of hits. You might be able to identify these items by looking at recent web server logs, or by spending a week or a month counting hits before doing the one-off reduction. Even without doing that, if there's a fundamental reason for their popularity (not just because they're currently rating high in your rankings and hence receiving traffic from people looking at your "most popular" chart), then hopefully they'll recover given time.</p>
    singulars
    1. This table or related slice is empty.
    1. POPopularity, How to make new hits count more than old hits?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSteve Jessop
    1. USSteve Jessop
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPopularity, How to make new hits count more than old hits?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI really like the reduce by small percentage everyday idea. With that there wont be a sharp cut off at 1 year ago but a gradual decay everyday. On your 3rd paragraph, maybe I could start by reducing by 10% times years active. So items added within a year will loose nothing and items from 3 years ago would loose 30%. Thanks.
      singulars
      1. PO
      1. USJD Isaacks
    2. CO@John: yes, I'm vague about the numbers deliberately, because you'll probably want to try different values and look at the results until you have a chart that's roughly what you want. Or if you don't know what you want, at least a chart that no longer definitely looks wrong to you or your boss :-)
      singulars
      1. PO
      1. USSteve Jessop
    3. COI thought of reducing by a fraction of the average views/day across *all* products (but not below 0).
      singulars
      1. PO
      1. USHugh Brackett
 

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