*Linnaea (Sion)

回复 10# 康-jingkangyu


要能直接存取数据库才能看到，论坛本身没这个功能。
另外我也是在自己的数据库副本上操作的，因为扫描全表太消耗资源了。
查询语句附：

1. SELECT authorid, FLOOR((dateline+28800)/604800) AS wk, COUNT(*) AS pcnt, SUM(CHAR_LENGTH(message)) AS scnt FROM clcn_posts WHERE tid != 3706 GROUP BY wk, authorid ORDER BY authorid, wk;

复制代码

绘图程序

1. <?php
   
2. set_time_limit(0);
   
3. $conn = new mysqli('127.0.0.1', '*', '*', '*');
   
4. $query = $conn->query('SELECT authorid, FLOOR((dateline+28800)/604800) AS wk, COUNT(*) AS pcnt, SUM(CHAR_LENGTH(message)) AS scnt FROM clcn_posts WHERE tid != 3706 GROUP BY wk, authorid ORDER BY authorid, wk;');
   
5. $pcnt = array();
   
6. $scnt = array();
   
7. while($data = $query->fetch_array(MYSQLI_ASSOC)) {
   
8.         $pcnt[$data['authorid']][$data['wk']] = $data['pcnt'];
   
9.         $scnt[$data['authorid']][$data['wk']] = $data['scnt'];
   
10. }
    
11. foreach($pcnt as $uid => $poststat) {
    
12.         $startwk = min(array_keys($poststat));
    
13.         $endwk = max(array_keys($poststat));
    
14.         echo "\rProcessing $uid...                ";
    
15.         if($endwk - $startwk < 4) continue;
    
16.         echo "\rProcessing $uid: Calculating...                ";
    
17.         $posts = array_sum($poststat);
    
18.         $maxposts = max($poststat);
    
19.        
    
20.         $lengthstat = $scnt[$uid];
    
21.         $maxlength = max($lengthstat);
    
22.        
    
23.         $im = imagecreatetruecolor(705, 455);
    
24.         $red = imagecolorallocate($im, 255, 0, 0);
    
25.         $blue = imagecolorallocate($im, 0, 0, 255);
    
26.         $black = imagecolorallocate($im, 0, 0, 0);
    
27.         $w = imagecolorallocate($im, 255, 255, 255);
    
28.        
    
29.         $style = array($w, $w, $red, $red, $red, $red, $red, $w, $w, $w);
    
30.         $styleS = array($w, $w, $blue, $blue, $blue, $blue, $blue, $w, $w, $w);
    
31.        
    
32.         echo "\rProcessing $uid: Building Frame...                ";
    
33.         imagefill($im, 0, 0, $w);
    
34.        
    
35.         imagelinethick($im, 50, 5, 50, 405, $black, 2);
    
36.         imagelinethick($im, 50, 405, 650, 405, $black, 2);
    
37.         imagelinethick($im, 650, 405, 650, 5, $black, 2);
    
38.        
    
39.         imagestring($im, 5, 30, 400, 0, $red);
    
40.         imagestring($im, 5, 20, 0, $maxposts, $red);
    
41.         imagestring($im, 5, 50, 415, $startwk, $black);
    
42.         imagestring($im, 5, 620, 415, $endwk, $black);
    
43.         imagestring($im, 5, 655, 400, 0, $blue);
    
44.         imagestring($im, 5, 655, 0, $maxlength, $blue);
    
45.        
    
46.         $wks = $endwk - $startwk;
    
47.         $chars = array_sum($lengthstat);
    
48.         $avglength = round($chars / $wks);
    
49.         $avgpost = round($posts / $wks, 2);
    
50.        
    
51.         $Y = 400 * $posts / $wks / $maxposts;
    
52.         $Ys = 400 * $chars / $wks / $maxlength;
    
53.         imagestring($im, 5, 5, 397 - $Y, $avgpost, $red);
    
54.         imagestring($im, 5, 655, 397 - $Ys, $avglength, $blue);
    
55.        
    
56.         imagesetstyle($im, $styleS);
    
57.         imageline($im, 50, 405 - $Ys, 650, 405 - $Ys, IMG_COLOR_STYLED);
    
58.         imagesetstyle($im, $style);
    
59.         imageline($im, 50, 405 - $Y, 650, 405 - $Y, IMG_COLOR_STYLED);
    
60.        
    
61.         $lastX = 0;
    
62.         $lastY = 400 * $poststat[$startwk] / $maxposts;
    
63.         $lastXs = 0;
    
64.         $lastYs = 400 * $lengthstat[$startwk] / $maxlength;
    
65.         imagefilledellipse($im, $lastXs + 50, 405 - $lastYs, 7, 7, $blue);
    
66.         imagefilledellipse($im, $lastX + 50, 405 - $lastY, 7, 7, $red);
    
67.        
    
68.         if($lengthstat[$startwk] == $maxlength) imagestring($im, 3, 50, 440, $startwk, $blue);
    
69.         if($poststat[$startwk] == $maxposts) imagestring($im, 3, 50, 430, $startwk, $red);
    
70.        
    
71.         for($i = 1; $i <= $wks; $i++) {
    
72.                 echo "\rProcessing $uid: Drawing $i / $wks...                ";
    
73.                 $X = 600 * $i / $wks;
    
74.                 $Y = 400 * $poststat[$i + $startwk] / $maxposts;
    
75.                 $Xs = 600 * $i / $wks;
    
76.                 $Ys = 400 * $lengthstat[$i + $startwk] / $maxlength;
    
77.                 imagelinethick($im, $lastXs + 50, 405 - $lastYs, $Xs + 50, 405 - $Ys, $blue, 2);
    
78.                 imagelinethick($im, $lastX + 50, 405 - $lastY, $X + 50, 405 - $Y, $red, 2);
    
79.                 imagefilledellipse($im, $Xs + 50, 405 - $Ys, 7, 7, $blue);
    
80.                 imagefilledellipse($im, $X + 50, 405 - $Y, 7, 7, $red);
    
81.                 $lastX = $X;
    
82.                 $lastY = $Y;
    
83.                 $lastXs = $Xs;
    
84.                 $lastYs = $Ys;
    
85.                
    
86.                 if($lengthstat[$i + $startwk] == $maxlength) imagestring($im, 3, $X + 50, 440, $i + $startwk, $blue);
    
87.                 if($poststat[$i + $startwk] == $maxposts) imagestring($im, 3, $X + 50, 430, $i + $startwk, $red);
    
88.         }
    
89.        
    
90.         echo "\rProcessing $uid: Saving file...                ";
    
91.         imagepng($im, $uid.'.png', 9);
    
92.         imagedestroy($im);
    
93. }
    
94. 
    
95. function imagelinethick($image, $x1, $y1, $x2, $y2, $color, $thick = 1)
    
96. {
    
97.     if ($thick == 1) {
    
98.         return imageline($image, $x1, $y1, $x2, $y2, $color);
    
99.     }
    
100.     $t = $thick / 2 - 0.5;
     
101.     if ($x1 == $x2 || $y1 == $y2) {
     
102.         return imagefilledrectangle($image, round(min($x1, $x2) - $t), round(min($y1, $y2) - $t), round(max($x1, $x2) + $t), round(max($y1, $y2) + $t), $color);
     
103.     }
     
104.     $k = ($y2 - $y1) / ($x2 - $x1); //y = kx + q
     
105.     $a = $t / sqrt(1 + pow($k, 2));
     
106.     $points = array(
     
107.         round($x1 - (1+$k)*$a), round($y1 + (1-$k)*$a),
     
108.         round($x1 - (1-$k)*$a), round($y1 - (1+$k)*$a),
     
109.         round($x2 + (1+$k)*$a), round($y2 - (1-$k)*$a),
     
110.         round($x2 + (1-$k)*$a), round($y2 + (1+$k)*$a),
     
111.     );
     
112.     imagefilledpolygon($image, $points, 4, $color);
     
113.     return imagepolygon($image, $points, 4, $color);
     
114. }
     
115. ?>

复制代码

PurpleFs

哈，貌似论坛重开正搭上我高三，估计我的数据会很低。

*Linnaea (Sion)

回复 12# PurpleFs

PurpleFs

回复  PurpleFs
Sion 发表于 2012-9-2 14:56

    哈，0.64，一帖都不够- - 看得出在哪一段时间恶复习状态。

苏陌寒 (苏陌寒)

.............除了对不起还是对不起.............跟前面的几位比起来，我觉得自己，真的很对不起。

?lyoko√?迪 (parkour小子)

我吗，图发的多，不太爱说话，请原谅。而且我只是一个p图的，不像黑幽灵是个资源帝