SQL   21

Aggregate Bitwise OR

Guest on 24th August 2022 04:33:06 AM

  1. CREATE TABLE V (
  2.   i INT
  3. )
  4. INSERT INTO V VALUES (64)
  5. INSERT INTO V VALUES (32)
  6. INSERT INTO V VALUES (16)
  7. GO
  8.  
  9. CREATE FUNCTION Agg_AND_helper (
  10.   @top INT
  11. ) RETURNS INT AS BEGIN
  12.   RETURN 0
  13. END
  14. GO
  15.  
  16. ALTER FUNCTION Agg_AND_helper (
  17.   @top INT
  18. ) RETURNS INT AS BEGIN
  19.   DECLARE @two_top INT
  20.   SET @two_top = POWER(2,@top)
  21.   DECLARE @mn INT, @mx INT
  22.   SELECT @mn = MIN(i%@two_top), @mx = MAX(i%@two_top) FROM V
  23.   IF @mn = @mx RETURN @mx
  24.   IF @mn = 0 OR @mx = 0 RETURN 0
  25.   SET @top = FLOOR(log(@mn)/log(2))
  26.   IF log(@mx)/log(2) < @top + 1
  27.     RETURN POWER(2,@top) + dbo.Agg_AND_helper(@top)
  28.   RETURN dbo.Agg_AND_helper(@top+1)
  29. END
  30. GO
  31.  
  32. CREATE FUNCTION Agg_AND (
  33. ) RETURNS INT AS BEGIN
  34.   DECLARE @mn INT, @mx INT
  35.   SELECT @mn = MIN(i), @mx = MAX(i) FROM V
  36.   IF @mn = @mx RETURN @mx
  37.   IF @mn = 0 OR @mx = 0 RETURN 0
  38.   DECLARE @top INT
  39.   SET @top = FLOOR(log(@mn)/log(2))
  40.   IF log(@mx)/log(2) < @top + 1
  41.     RETURN POWER(2,@top) + dbo.Agg_AND_helper(@top)
  42.   RETURN dbo.Agg_AND_helper(@top+1)
  43. END
  44. GO
  45.  
  46. CREATE FUNCTION Agg_OR_helper (
  47.   @top INT
  48. ) RETURNS INT AS BEGIN
  49.   RETURN 0
  50. END
  51. GO
  52.  
  53. ALTER FUNCTION Agg_OR_helper (
  54.   @top INT
  55. ) RETURNS INT AS BEGIN
  56.   DECLARE @two_top INT
  57.   SET @two_top = POWER(2,@top)
  58.   DECLARE @mx INT
  59.   SELECT @mx = MAX(i%@two_top) FROM V
  60.   IF @mx = 0 RETURN 0
  61.   SET @top = FLOOR(log(@mx)/log(2))
  62.   RETURN CASE WHEN @mx = 0 THEN 0
  63.          ELSE POWER(2,@top) + dbo.Agg_OR_helper(@top) END
  64. END
  65. GO
  66.  
  67. CREATE FUNCTION Agg_OR (
  68. ) RETURNS INT AS BEGIN
  69.   DECLARE @mx INT
  70.   SELECT @mx = MAX(i) FROM V
  71.   IF @mx = 0 RETURN 0
  72.   DECLARE @top INT
  73.   SET @top = FLOOR(log(@mx)/log(2))
  74.   RETURN POWER(2,@top) + dbo.Agg_OR_helper(@top)
  75. END
  76. GO
  77. SELECT dbo.Agg_AND()
  78. SELECT dbo.Agg_OR()
  79. GO
  80.  
  81. DROP FUNCTION Agg_AND
  82. DROP FUNCTION Agg_AND_helper
  83. DROP FUNCTION Agg_OR
  84. DROP FUNCTION Agg_OR_helper
  85. DROP TABLE V

Raw Paste


Login or Register to edit or fork this paste. It's free.