JavaScript ES6, 18 bytes

n=>-~(n/3)*(1-n%3)

Turned out very similar to @LeakyNun's answer but I didn't see his until after I posted mine.

Explanation and Ungolfed

-~ is shorthand for Math.ceil, or rounding up:

n =>               // input in var `n`
    Math.ceil(n/3) // Get every 3rd number 1,1,1,2,2,2, etc.
    *
    (1-n%3)        // 1, 0, -1, 1, 0, -1, ...

function f(n){n=i.value;o.value=-~(n/3)*(1-n%3);}

Input: <input id=i oninput="f()"/><br /><br />
Output: <input id=o readable/>

Python 2, 24 bytes

lambda n:(n/3+1)*(1-n%3)

Full program:

a=lambda n:(n/3+1)*(1-n%3)

print(a(0))   #   1
print(a(11))  #  -4
print(a(76))  #   0
print(a(134)) # -45
print(a(296)) # -99

Jelly, 7 bytes

+6d3’PN

Zero-indexed. Test cases here.

Explanation:

+6      Add 6:     x+6
d3      Divmod:    [(x+6)/3, (x+6)%3]
’       Decrement: [(x+6)/3-1, (x+6)%3-1]
P       Product    ((x+6)/3-1) * ((x+6)%3-1)

MarioLANG, 93 bytes

one-indexed

Try It Online

;
>(-))+(-      !
"=============#
 :(![<:![<:)![<
 !=#="!#="!=#="
!  < !-< !- <
#==" #=" #=="

Explanation :

we begin by taking the imput

;

wich give us

          v
... 0 0 input 0 0 ...

we then decrement the left byte and increment the right byte with

;
>(-))+(       !
"=============#

we end up with

           v
... 0 -1 input +1 0 ...

we then set up the loop

;
>(-))+(-      !
"=============#
   ![< ![<  ![<
   #=" #="  #="
!  < !-< !- <
#==" #=" #=="

the loop will go until the memory look like

         v 
... 0 -X 0 +X 0 ...

we then only need to output the result

;
>(-))+(-      !
"=============#
 :(![<:![<:)![<
 !=#="!#="!=#="
!  < !-< !- <
#==" #=" #=="

MATL, 8 bytes

:t~y_vG)

The result is 1-based.

Try it online!

Explanation

This builds the 2D array

 1  2  3  4  5 ...
 0  0  0  0  0 ...
-1 -2 -3 -4 -5 ...

and then uses linear indexing to extract the desired term. Linear indexing means index down, then across (so in the above array the first entries in linear order are 1, 0, -1, 2, 0, ...)

:     % Vector [1 2 ... N], where N is implicit input
t~    % Duplicate and logical negate: vector of zeros
y_    % Duplicate array below the top and negate: vector [-1 -2 ... -N]
v     % Concatenate all stack contents vertically
G)    % Index with input. Implicit display

MATL, 15 12 bytes

3/XkG3X\2-*_

This uses one based indexing.

Try it online! or verify test cases

Explanation:

    G          #Input
     3X\       #Modulus, except multiples of 3 give 3 instead of 0
        2-     #Subtract 2, giving -1, 0 or 1
3/Xk           #Ceiling of input divided by 3.
          *    #Multiply 
           _   #Negate

Perl 6,  26  23 bytes

{({|(++$,0,--$)}...*)[$_]}
{($_ div 3+1)*(1-$_%3)}

( The shorter one was translated from other answers )

Explanation (of the first one):

{ # bare block with implicit parameter ｢$_｣
  (

    # start of sequence generator

    { # bare block
      |(  # slip ( so that it flattens into the outer sequence )
        ++$, # incrementing anon state var =>  1, 2, 3, 4, 5, 6
        0,   # 0                           =>  0, 0, 0, 0, 0, 0
        --$  # decrementing anon state var => -1,-2,-3,-4,-5,-6
      )
    }
    ...  # repeat
    *    # indefinitely

    # end of sequence generator

  )[ $_ ] # get the nth one (zero based)
}

Test:

#! /usr/bin/env perl6
use v6.c;
use Test;

# store it lexically
my &alt-seq-sign = {({|(++$,0,--$)}...*)[$_]}
my &short-one = {($_ div 3+1)*(1-$_%3)}

my @tests = (
    0 =>   1,
   11 =>  -4,
   76 =>   0,
  134 => -45,
  296 => -99,
  15..^30  => (6,0,-6,7,0,-7,8,0,-8,9,0,-9,10,0,-10)
);

plan @tests * 2 - 1;

for @tests {
  is alt-seq-sign( .key ), .value, 'alt-seq-sign  ' ~ .gist;

  next if .key ~~ Range; # doesn't support Range as an input
  is short-one(    .key ), .value, 'short-one     ' ~ .gist;
}

1..11
ok 1 - alt-seq-sign  0 => 1
ok 2 - short-one     0 => 1
ok 3 - alt-seq-sign  11 => -4
ok 4 - short-one     11 => -4
ok 5 - alt-seq-sign  76 => 0
ok 6 - short-one     76 => 0
ok 7 - alt-seq-sign  134 => -45
ok 8 - short-one     134 => -45
ok 9 - alt-seq-sign  296 => -99
ok 10 - short-one     296 => -99
ok 11 - alt-seq-sign  15..^30 => (6 0 -6 7 0 -7 8 0 -8 9 0 -9 10 0 -10)

Pyke, 8 7 bytes (old version)

3.DeRt*

Try it here! - Note that link probably won't last for long

3.D      - a,b = divmod(input, 3)
   e     - a = ~a -(a+1)
     t   - b -= 1
      *  - a = a*b
         - implicit output a

Newest version

3.DhRt*_

Try it here!

3.D      - a,b = divmod(input, 3)
   h     - a+=1
     t   - b-=1
      *  - a = a*b
       _ - a = -a
         - implicit output a

MATL, 8 bytes

_3&\wq*_

This solution uses 1-based indexing into the sequence.

Try it Online

Modified version showing all test cases

Explanation

        % Implicitly grab the input
_       % Negate the input
3&\     % Compute the modulus with 3. The second output is floor(N/3). Because we negated
        % the input, this is the equivalent of ceil(input/3)
w       % Flip the order of the outputs
q       % Subtract 1 from the result of mod to turn [0 1 2] into [-1 0 1]
*       % Take the product with ceil(input/3)
_       % Negate the result so that the sequence goes [N 0 -N] instead of [-N 0 N]
        % Implicitly display the result

Pyth, 10 bytes

*h/Q3-1%Q3

Try it online!

Explanation:

*     : Multiply following two arguments
h/Q3  : 1 + Input/3
-1%Q3 : 1 - Input%3

Note: I've assumed the zero-indexed sequence.

J, 19 15 bytes

>.@(%&3)*1-3|<:

Probably need to golf this further...

1-indexed.

Ungolfed:

>> choose_sign      =: 1-3|<:      NB. 1-((n-1)%3)
>> choose_magnitude =: >.@(%&3)    NB. ceil(n/3)
>> f                =: choose_sign * choose_magnitude
>> f 1 12 77
<< 1 _4 0

Where >> means input (STDIN) and << means output (STDOUT).

Batch, 30 bytes

@cmd/c set/a(1+%1/3)*(1-%1%%3)

The cmd/c makes set/a echo the result of the calculation.

J, 27 bytes

Whilst not the golfiest, I like it better, as it uses an agenda.

>.@(>:%3:)*1:`0:`_1:@.(3|])

Here is the tree decomposition of it:

         ┌─ >.      
  ┌─ @ ──┤    ┌─ >: 
  │      └────┼─ %  
  │           └─ 3: 
  ├─ *              
──┤           ┌─ 1: 
  │      ┌────┼─ 0: 
  │      │    └─ _1:
  └─ @. ─┤          
         │    ┌─ 3  
         └────┼─ |  
              └─ ]  

This is very similar to Kenny's J answer, in that it chooses the magnitude and sign, but it's different in that I use an agenda to choose the sign.

MATLAB / Octave, 27 bytes

@(n)ceil(n/3)*(mod(-n,3)-1)

This creates an anonymous function that can be called using ans(n). This solution uses 1-based indexing.

All test cases

Actually, 10 bytes

3@│\u)%1-*

Try it online!

Explanation:

3@│\u)%1-*
3@│         push 3, swap, duplicate entire stack ([n 3 n 3])
   \u)      floor division, increment, move to bottom ([n 3 n//3+1])
      %1-   mod, subtract from 1 ([1-n%3 n//3+1])
         *  multiply ([(1-n%3)*(n//3+1)])

Retina, 45 bytes

.+
11$&$*
(111)+(1)*
$#2$#1
T`d`+0-`^.
^0.+
0

Try it online!

Test suite.

Takes input/output in base-ten. 1-indexed.

Unary input, base-ten output, 1-indexed: 40 bytes

$
11
(111)+(1)*
$#2$#1
T`d`+0-`^.
^0.+
0

Try it online!

Test suite.

Bash, 80 Bytes

b=$(($1%3))
[ $b -eq 1 ]&&echo 0||{
[ $b -eq 2 ]&&a=-
a=$a$((1+$1/3))
echo $a
}

Bash + GNU Coreutils, 30 Bytes

bc<<<"(1+$1/3)*(1-$1%3)"

I think, <<< is a Bash-ism.

Perl 5, 22 bytes

21 plus one for -p:

$_=(-$_,$_+2)[$_%3]/3

Uses 1-based indexing.

Explanation:

-p sets the variable $_ equal to the input. The code then sets it equal to the $_%3th element, divided by 3, of the 0-based list (-$_,$_+2) (where % is modulo). Note that if $_%3 is two, then there is no such element, and the subsequent division by 3 numifies the undefined to 0. -p then prints $_.

Mathematica 26 bytes

With 4 bytes saved thanks to Martin Ender.

⎡#/3⎤(-#~Mod~3-1)&

Uses the same approach as Suever.

Haskell, 27 bytes

f x=div(x+3)3*(1-mod(x+3)3)

Slightly more interesting 28 byte solution:

(((\i->[i,0,-i])=<<[1..])!!)

(Both are 0-indexed)