应用排序算法，数据库查询 [英] Apply sorting algorithm to database query

问题描述

我想再拍列表热点如 reddit的。

我发现这个话题它解释了如何自己的排序算法的工作原理

I found this topic where it explains how their sorting algorithm works

首先，我想问一下，如果是合法使用他们的算法？

First I want to ask, if it is legal to use their algorithm?

，如果是，我将如何运用它到PHP数据库查询。 我是否需要先选择的所有帖子，然后排序呢？

And if yes, how would I apply it to PHP database query. Do I need to SELECT all posts first and then sort it?

function hot($ups, $downs, $date) {
    $s = $ups - $downs;
    $order = log(max(abs(s), 1), 10);
    if(s > 0) {
        $sign = 1;
    } elseif(s < 0) {
        $sign = -1;
    } else {
        $sign = 0;
    }
    $date = new DateTime($date);
    $seconds = $date->format('U');
    return round($order + $sign * $seconds / 45000, 7);
}

这是什么，当我将它转换为PHP我搞定了。

this is what I get when I convert it to PHP.

推荐答案

假设你的跌宕起伏列称为 UPS 和跌，则是这样的：

Assuming your ups and downs columns are called ups and downs, then something like:

ORDER BY ROUND(
    ( LOG10(
          GREATEST(
              ABS(`ups` - `downs`), 
              1
          )
      ) + 
      SIGN(`ups` - `downs`) *
      UNIX_TIMESTAMP(`date_posted`)  / 45000
    ),
    7
)

这可能是一个更好的主意，用这个公式在您的选择列表中创建一个计算列，然后才能按该列

It might be a better idea to use this formula to create a calculated column in your select list, and then order by that column

修改

计算列的例子：

SELECT `ups`,
       `downs`,
       `posted_date`,
       ROUND(
           ( LOG10(
               GREATEST(
                   ABS(`ups` - `downs`), 
                   1
               )
             ) + 
             SIGN(`ups` - `downs`) *
             UNIX_TIMESTAMP(`date_posted`)  / 45000
           ),
           7
       ) AS hotness
  FROM `posts`

所以辣味是计算列

这篇关于应用排序算法，数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！