每天随机选择50个文件

Question

我试图选择50个随机文件，每天从2012年10月1日。例如：

• 10月1日= 50份档案
• 10月2日= 50份档案
• 10月3日= 50份档案
• Etc.....today

到目前为止，我可以选择一个文件每天，但我需要50个。我希望他们是随机的

DECLARE @DaysBack AS INT
SET @DaysBack = -25


    SET NOCOUNT ON;

    SELECT FileDate, MAX(FileName)  FROM (
    SELECT 
    CONVERT(VARCHAR(10),DATEADD(second,actiondate, CAST('1970-01-01 00:00:00' AS datetime)), 101) AS FileDate

    ,'\\directory\' + filename AS FileName
    FROM   Tableq q
    JOIN tablec c
    on  q.projectid = c.projectid
           AND actiondate >= Datediff(s, '19700101 00:00:00:000',
                             Dateadd(DAY, @DaysBack, Getutcdate()))


    ) x

    GROUP BY FileDate
    ORDER  BY FileDate 

Answer 1

我目前无法访问Server实例(或SQL )。但这应该给出每个行动日期的随机id ..。

• ROW_NUMBER() OVER (PARTITION BY actiondate ORDER BY NEWID())

这意味着以下人员将在每个行动日期随机挑选50项.

DECLARE @DaysBack AS INT
SET @DaysBack = -25

SET NOCOUNT ON;

SELECT
  FileDate, FileName
FROM
(
  SELECT 
    DATEADD(DAY, actiondate / 86400, '19700101') AS FileDate,
    '\\\\directory\\' + filename AS FileName,
    ROW_NUMBER() OVER (PARTITION BY actiondate / 86400 ORDER BY NEWID()) AS random_daily_id
FROM
  Tableq q
JOIN
  tablec c
    ON  q.projectid = c.projectid
    AND actiondate >= Datediff(s, '19700101', Dateadd(DAY, @DaysBack, Getutcdate()))
) x
WHERE
  random_daily_id <= 50
ORDER BY
  FileDate 

Answer 2

假设您使用的是Server 2005或更高版本，则可以使用排序函数来执行此操作：

SELECT FileDate, MAX(FileName)
FROM (select x.*,
             ROW_NUMBER() over (partition by FileDate order by newid()) as seqnum
      from (SELECT CONVERT(VARCHAR(10),DATEADD(second,actiondate, CAST('1970-01-01 00:00:00' AS datetime)), 101
                          ) AS FileDate,
                   '\\directory\' + filename AS FileName
            FROM   Tableq q JOIN
                   tablec c
                   on  q.projectid = c.projectid and
                       actiondate >= Datediff(s, '19700101 00:00:00:000',
                                     Dateadd(DAY, @DaysBack, Getutcdate()))
           ) x
     ) x
where seqnum <= 50
ORDER  BY FileDate

这使用row_number()为每个日期的文件添加序列号，并随机分配编号。然后，它为每个日期选择前50位。

Answer 3

I建议您不要实现任何随机抽取数据库端。我在一个项目中也有类似的需求，但最终我将随机化逻辑转移到了业务方面。