windows.exs (2195B)
1 defmodule Ecto.Integration.WindowsTest do 2 use Ecto.Integration.Case, async: Application.compile_env(:ecto, :async_integration_tests, true) 3 4 alias Ecto.Integration.TestRepo 5 import Ecto.Query 6 7 alias Ecto.Integration.{Comment, User, Post} 8 9 test "over" do 10 u1 = TestRepo.insert!(%User{name: "Tester"}) 11 u2 = TestRepo.insert!(%User{name: "Developer"}) 12 c1 = TestRepo.insert!(%Comment{text: "1", author_id: u1.id}) 13 c2 = TestRepo.insert!(%Comment{text: "2", author_id: u1.id}) 14 c3 = TestRepo.insert!(%Comment{text: "3", author_id: u1.id}) 15 c4 = TestRepo.insert!(%Comment{text: "4", author_id: u2.id}) 16 17 # Over nothing 18 query = from(c in Comment, select: [c, count(c.id) |> over()]) 19 assert [[^c1, 4], [^c2, 4], [^c3, 4], [^c4, 4]] = TestRepo.all(query) 20 21 # Over partition 22 query = from(c in Comment, select: [c, count(c.id) |> over(partition_by: c.author_id)]) 23 assert [[^c1, 3], [^c2, 3], [^c3, 3], [^c4, 1]] = TestRepo.all(query) 24 25 # Over window 26 query = from(c in Comment, windows: [w: [partition_by: c.author_id]], select: [c, count(c.id) |> over(:w)]) 27 assert [[^c1, 3], [^c2, 3], [^c3, 3], [^c4, 1]] = TestRepo.all(query) 28 end 29 30 test "frame" do 31 posts = Enum.map(0..6, &%{counter: &1, visits: round(:math.pow(2, &1))}) 32 TestRepo.insert_all(Post, posts) 33 34 n = 1 35 query = from(p in Post, 36 windows: [w: [order_by: p.counter, frame: fragment("ROWS BETWEEN ? PRECEDING AND ? FOLLOWING", ^n, ^n)]], 37 select: [p.counter, sum(p.visits) |> over(:w)] 38 ) 39 assert [[0, 3], [1, 7], [2, 14], [3, 28], [4, 56], [5, 112], [6, 96]] = TestRepo.all(query) 40 41 query = from(p in Post, 42 windows: [w: [order_by: p.counter, frame: fragment("ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING")]], 43 select: [p.counter, sum(p.visits) |> over(:w)] 44 ) 45 assert [[0, 126], [1, 124], [2, 120], [3, 112], [4, 96], [5, 64], [6, nil]] = TestRepo.all(query) 46 47 query = from(p in Post, 48 windows: [w: [order_by: p.counter, frame: fragment("ROWS CURRENT ROW")]], 49 select: [p.counter, sum(p.visits) |> over(:w)] 50 ) 51 assert [[0, 1], [1, 2], [2, 4], [3, 8], [4, 16], [5, 32], [6, 64]] = TestRepo.all(query) 52 end 53 end